how do i sort dates truly chronologically?

[helios]

I have a subform representing a subdatasheet's records. I am trying to sort
these based on chronological order. I'm hoping there's a really simple way
to sort by date that works in a form. Presently, I have extracted DateParts
in a query based on the subdatasheet and tried to use that as the datasource
for the subform, hoping that the sort order would show up on the form.
Instead, the dates are sorted as if they were simple numbers. Shall I leave
a nasty message on the Developers' Site about how Access handles Date/Time
Formats, or can someone tell me how to make this sort as if I could use
grouping levels like on reports.

 

[Rick Brandt]

I have a subform representing a subdatasheet's records. I am trying
to sort these based on chronological order. I'm hoping there's a
really simple way to sort by date that works in a form. Presently, I
have extracted DateParts in a query based on the subdatasheet and
tried to use that as the datasource for the subform, hoping that the
sort order would show up on the form. Instead, the dates are sorted
as if they were simple numbers. Shall I leave a nasty message on the
Developers' Site about how Access handles Date/Time Formats, or can
someone tell me how to make this sort as if I could use grouping
levels like on reports.

If they are actual DateTime DataTypes and you haven't wrapped them in the
format function then they should sort chronologically. What exactly do you
mena whne you say...

"...I have extracted DateParts in a query..."

If you used the DatePart() function to pull out the numerical values for
Day, Month, Year and are sorting on those then they will sort as numbers
because that is what they are.

 

[John Vinson]

I have a subform representing a subdatasheet's records. I am trying to sort
these based on chronological order. I'm hoping there's a really simple way
to sort by date that works in a form. Presently, I have extracted DateParts
in a query based on the subdatasheet and tried to use that as the datasource
for the subform, hoping that the sort order would show up on the form.
Instead, the dates are sorted as if they were simple numbers. Shall I leave
a nasty message on the Developers' Site about how Access handles Date/Time
Formats, or can someone tell me how to make this sort as if I could use
grouping levels like on reports.

Sounds like you're trying too hard.

An Access Date/Time field will indeed sort chronologically. If you use
Format() to convert it to a string, or DatePart() to extract pieces of
it, you'll be doing a lot of extra work for no or negative benefit.

Try basing the Subform (DON'T use table datasheets or subdatasheets
for *any* purpose other than debugging!) on a Query with Ascending as
the sort operand on the date field. Should work just fine.

John W. Vinson[MVP]