Re: Subquery or not ?
- From: "Gary Walter" <gary@xxxxxxxxxxx>
- Date: Mon, 29 Jan 2007 13:05:31 -0600
strDate="30/11/2006"
?DateSerial(Right(strDate,4),Mid(strDate,4,2),Left(strDate,2))
11/30/2006
"Nicodemus" wrote:
Hi Jason,
thx for your feedback.
You can imagine that I don't use a txt field just for the fun of it. This
is
the way it is built in the table I'm linking to via an ODBC connection,
and I
don't have the privilige to change it.
Nope, it's absolutely right, text fields sort from left to right, so 3
is greater than 2 so it definitely is the maximum.
That's why I manipulate the text to get the data as yyyymmdd
"Jason Lepack" wrote:
The REAL question is why did you make it so difficult on yourself and
not just use a date/time field?
My query looks like :
SELECT Max(test_date.creationdate) AS MaxOfcreationdate FROM test_date;
The outcome is "31/12/2006", which is wrong because it should be
"26/01/2007".
Nope, it's absolutely right, text fields sort from left to right, so 3
is greater than 2 so it definitely is the maximum.
On Jan 29, 12:49 pm, Nicodemus <Nicode...@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:
Hello,
I try to extract the latest date from a table "Test_date", having a
Text
field "creationdate" containing dates (format dd/mm/yyyy) :
creationdate
26/01/2007
22/01/2007
15/01/2007
07/01/2007
31/12/2006
23/12/2006
30/11/2006
To retrieve "26/01/2007", I changed my query as follow :
SELECT test_date.creationdate, Right([creationdate],4) &
Mid([creationdate],4,2) & Left([creationdate],2) AS last_date
FROM test_date
ORDER BY Right([creationdate],4) & Mid([creationdate],4,2) &
Left([creationdate],2) DESC;
This query actually doesn't extract the latest date (max), it just
sorts the
"last_date", in descending order.
I'd like to group the outcome like this :
SELECT test_date.creationdate, Max(Right([creationdate],4) &
Mid([creationdate],4,2) & Left([creationdate],2)) AS last_date
FROM test_date;
but it doesn't work !
Should I use a subquery or something ?
I know I could do the job with 2 queries, but I wonder if there is a
way to
do it with one.
I hope my explanations are clear enough.
thx in advance for any help,
Nicodemus
.
- References:
- Subquery or not ?
- From: Nicodemus
- Re: Subquery or not ?
- From: Jason Lepack
- Re: Subquery or not ?
- From: Nicodemus
- Subquery or not ?
- Prev by Date: Rounding problems with query
- Next by Date: Previous Month name
- Previous by thread: Re: Subquery or not ?
- Next by thread: Can't update data in query..
- Index(es):
Relevant Pages
|
