Re: Subquery or not ?
- From: Nicodemus <Nicodemus@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 29 Jan 2007 10:41:00 -0800
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
- Follow-Ups:
- Re: Subquery or not ?
- From: Gary Walter
- Re: Subquery or not ?
- References:
- Subquery or not ?
- From: Nicodemus
- Re: Subquery or not ?
- From: Jason Lepack
- Subquery or not ?
- Prev by Date: Re: Subquery or not ?
- Next by Date: Rounding problems with query
- Previous by thread: Re: Subquery or not ?
- Next by thread: Re: Subquery or not ?
- Index(es):
Relevant Pages
|
