Date not Sorting Correctly (screenshot attached)

[KrenzyRyan]

I cannot seem to figure this one out. The date is not sorting correctly on this query, see below:



Any thoughts?

[boblarson]

Your dates are not the date/time datatype. They are TEXT. So in order for the sorting to work you need to either have them as that data type or change the field in the query by using CDate or by using your own custom group in the report and use CDate.

[KrenzyRyan]

Excellent, thanks Bob.

[KrenzyRyan]

Hmm, now the time isn't sorting correctly. I want it to sort ascending, and its set as a Date/Time field.

[KrenzyRyan]

The query does it correctly, but the report mixes it up...

[boblarson]

You need to create a new field using the date and time fields concatenated:

Select Cdate([DateFieldNameHere]) + CDate([TimeFieldNameHere]) As MyDateTime...etc.

And then you would use that field name (MyDateTime or whatever you called it) in the Sorting And Grouping of the report to sort on.