Problem with sorting dates

[Guest]

I have a field [TrackDate] which I formatted to be
Months: Format$([TrackDate],"mmmm yyyy",0,0) but when I sort the months,
they are sorted wrongly as follows April, June,May
Could someone help please!

 

[Douglas J Steele]

April, June, May is the correct sort order for what you've told Access to
do: you've converted the dates into strings, and it's sorting those strings!

If you want it to sort properly, sort on a pure date field, not a formatted
one. Since you've already got the pure date in the table, just sort on it.

 

[Guest]

I also have the names of the months that I need to sort. Mine are a text
field not a date field because it only holds the month and not the day or
year. I know I can create a new field with numbers in the correct order and
sort that field instead but didn't there used to be a way to enter data into
the Order By property so they appeared on the report in the order you
want...January, February, etc.?

 

[Rick Brandt]

I also have the names of the months that I need to sort. Mine are a
text field not a date field because it only holds the month and not
the day or year. I know I can create a new field with numbers in the
correct order and sort that field instead but didn't there used to be
a way to enter data into the Order By property so they appeared on
the report in the order you want...January, February, etc.?

You're possibly thinking of the ColumnHeading property of a Crosstab query.
In a normal query the sort is on the data you have. You either need to
format the text such that an alpha sort gives you the desired order or join
to another table that translates your text values to a numeric field that
you can sort on.

 

[Van T. Dinh]

I guess you can use a Translation / Lookup Table to translate Jan to 1, ...,
Dec to 12.

Incorporate this Translation Table into your Report RecordSource and then
you can sort by the translated numbers but still print the Month names.

--
HTH
Van T. Dinh
MVP (Access)

I also have the names of the months that I need to sort. Mine are a text
field not a date field because it only holds the month and not the day or
year. I know I can create a new field with numbers in the correct order and
sort that field instead but didn't there used to be a way to enter data into
the Order By property so they appeared on the report in the order you
want...January, February, etc.?

I have a field [TrackDate] which I formatted to be
Months: Format$([TrackDate],"mmmm yyyy",0,0) but when I sort the months,
they are sorted wrongly as follows April, June,May
Could someone help please!

 

[Guest]

April, June, May is the correct sort order for what you've told Access to
do: you've converted the dates into strings, and it's sorting those
strings!

If you want it to sort properly, sort on a pure date field, not a
formatted
one. Since you've already got the pure date in the table, just sort on it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a field [TrackDate] which I formatted to be
Months: Format$([TrackDate],"mmmm yyyy",0,0) but when I sort the months,
they are sorted wrongly as follows April, June,May
Could someone help please!