Subquery or not ?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

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".

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
 
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".
Click to expand...

Nope, it's absolutely right, text fields sort from left to right, so 3
is greater than 2 so it definitely is the maximum.


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
Click to expand...
 
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.
Click to expand...

That's why I manipulate the text to get the data as yyyymmdd


Jason Lepack said:
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".
Click to expand...

Nope, it's absolutely right, text fields sort from left to right, so 3
is greater than 2 so it definitely is the maximum.


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
Click to expand...
Click to expand...
 
strDate="30/11/2006"
?DateSerial(Right(strDate,4),Mid(strDate,4,2),Left(strDate,2))
11/30/2006

Nicodemus said:
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.
Click to expand...

That's why I manipulate the text to get the data as yyyymmdd


Jason Lepack said:
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".
Click to expand...

Nope, it's absolutely right, text fields sort from left to right, so 3
is greater than 2 so it definitely is the maximum.


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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Formula help 3
Seeking Import/Link/Query workaround #Num! and #Error responses 6
finding the max of a subquery 1
Need only most recent record for each... 3
subquery to return max date misses some records altogether? 2
query 2
Sequence number 9
Selecting whole records while using a MaxOf function. 5

Back
Top