I am using the following query:

SELECT [Data and Names_Crosstab].Date,
[Data and Names_Crosstab].Diamond,
[Data and Names_Crosstab].Opponent,
[Data and Names_Crosstab].Record,
[Data and Names_Crosstab].Result,
[Data and Names_Crosstab].Time
FROM [Data and Names_Crosstab]
ORDER BY [Data and Names_Crosstab].Date;

The original table from which [Data and Names_Crosstab].Date is selected is formatted as a text field. The reason for this is long and convoluted, but necessary. My ORDER BY is therefore doing a text sort on the dates rather than a date sort.

In my SQL Server books I have found CAST and CONVERT functions that I believe would work to correct the sort thus:

ORDER BY (CAST([Data and Names_Crosstab].Date, Date/Time));

CAST, however, is an "Undefined Function" in Access. Is my assumption above correct about how CAST would work?

How do I achieve the same functionality in Access?

Thanks in advance.

Rich

__________________
Ego is a faithful friend; He stays with us all the way to the crater.

Depends on what is actually in the field but you might try CDate. I believe Date and Time are reserved words in Access so these fields should be:
--

Joe

That worked just as advertised. I searched online and in book indices for date format, format, convert, case, cast, etc. and never came upon "CDate". If one does not know that "CDate" exists, how should I have gone about finding it???

Thanks a bunch!!!
Rich

Well it's not a legitimate ANSI SQL function, nor an Access one either. It works because Access allows you to use any function in the Visual Basic for Applications (VBA) library. If you go into Modules | New then press F2 and change to VBA in the drop down at the top left you can see all the other ones and then highlight them and press F1 for more information.

--

Joe

I also tried Google. 'Covert String Date Access' produced a lot of answers in the first few hits.

--

Joe

Thanks again for your help!!

Rich

If you ever need a function but don't know if one exists, search help for one that DOES exist and then click the "See Also" link. There you may find "Functions" or "Date Functions" or "Text Functions" or some link like that. Clicking it will then give you the list that Access allows.

I have found if you search Access help DIRECTLY for "Date Functions" right off the bat, it won't find what you want, but if you actually search for a known one and click the "See Also" link, you'll get them. Weird, huh?

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division