Query Problem with convert

[John Wright]

I have the following query I inherited for a report

SELECT TOP 20 File, Ingot, Traveler, Mult, ShopOrder_Item,
Thickness_Mean, Thickness_StDev,EndTime
FROM Coil_Stats
WHERE (Thickness_Mean > 0) AND (Thickness_StDev > 0) and
Thickness_Nominal = 0.026
GROUP BY Ingot, Traveler, Mult, ShopOrder_Item, Thickness_Mean, File,
Thickness_StDev,EndTime
ORDER BY EndTime


The person who designed the database made the EndTime field a text field.
Typical data in this field would be "4/14/2006 8:08:05 AM" (without quotes).
I have a report that must run and I want to use the following query

SELECT TOP 20 File, Ingot, Traveler, Mult, ShopOrder_Item,
Thickness_Mean, Thickness_StDev,CDATE(EndTime)
FROM Coil_Stats
WHERE (Thickness_Mean > 0) AND (Thickness_StDev > 0) and
Thickness_Nominal = 0.026
GROUP BY Ingot, Traveler, Mult, ShopOrder_Item, Thickness_Mean, File,
Thickness_StDev,EndTime
ORDER BY EndTime DESC

I can run the query without any problem, but the conversion function does
not convert the field then sort. I am still getting a sort based on the
text field. So if I have a date of "9/19/2006 3:05:04 PM" and a date of
"11/15/2006 12:30:33 PM" and a date of "10/06/2006 1:12:50 AM" when I do the
sort the 10/06 date is first then 11/15 then 9/19. I need only the last 20
dates and times in this table and I can't seem to get this to work. I am a
T-SQL and PL/SQL developer and could figure this out, but this has me
stumped. Any help on writing this query would be great. All I need is the
last 20 records inserted into the database.

John Wright

 

[Jeff Boyce]

John

Take a look at the CDate() function, which converts a vaguely date-like
string into an Access date/time value. Do this in a query to start with, to
see if all your text-based dates can be converted. If they can be, consider
using an update query (and a new date/time field in the table) to convert
them permanently. Of course, this might also require you to change any
queries, forms, reports, macros, and/or code that relied on the old text
field.

If you don't chose to go to all this work, just use your first query (a
select query with the conversion function) to generate what you need.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Wright]

I did try to use the select with a conversion function and it did not return
the data I needed. It still orders the data as a string. Can you provide
and example of the query?

John

 

[Guest]

Hi John,

Try this (I'm not sure if it will work):

ORDER BY CDate([EndTime]) DESC

However, I agree with Jeff that you will be better off in the long run to
change the data type to a date/time field.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________