Format "Short Date

[dbero]

I am working with an Access 2000 front-end linked to an Oracle back-end. One of the fields is foramated as "7/9/2006 3:41:01 PM". In my query I am using the SQL "Format([R6T_QVLOG].[QVL_COMPLETE_DT],"Short Date") AS NewDate" to format to a useable date.

However, this newly formatted date is not working because it sorts 7/9/06 as a greater date than 7/10/06, and misses some dates.

It seems as though the Access format function does not properly re-format the Oracle format. Any suggestions?

 

[PHV]

The access format function returns a string, not a date, so the sort is done lexicographically, not chronologically.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dbero]

Is there a method to re-format as a date, rather than a string? I do need it to properly identify the physical date.

thank you

 

[PHV]

Formatting is not a data retrieval issue, but a data presentation one.
So, unless R6T_QVLOG.QVL_COMPLETE_DT is not seen as a DateTime field by access, SQL is not concerned.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886