Sorting a date field

[Guest]

Okay I have a subreport that is grouped by Month & year, but the Month is
formated as January, 2006. It is sorting as alphnumeric. Is there away to
change this sorting to follow January, Febuary, March instead of Febuary,
January, March?

Thank you for any help in this matter,
Pixie

 

[Guest]

I don't know how you have your sorting set up, but here is a formula that
will convert your literal Month and Year to a string that will sort it like
you need it:
=format(Month(cdate("January, 2006")) & year(cdate("January, 2006")),"000000")
will return 012006
Then
=format(Month(cdate("February, 2006")) & year(cdate("Febuary,
2006")),"000000")
will return 022006
etc.

 

[Guest]

If you use the Sort and Grouping in Access it should sort correctly no matter
how you format the display.
Open the report in design view and click on menu VIEW - Sorting And
Grouping. Select your date field in the popup window.

 

[Guest]

I have done the sorting but it still sorts alphnumeric and Febuary comes
before January and March comes after January. I want them to be January,
Feburary, March, etc....

 

[Duane Hookom]

Assuming you have a date field in your report's record source, set your
sorting and grouping expression to:

=Month([YourDateField])

 

[Guest]

This is how I have it formated. HOw can I change this so it will sort by
month and in the correct monthly order?
DATE By Month: Format$([MAINTABLE].[DATE],'mmmm yyyy')
THanks,
Pixie

 

[Duane Hookom]

Just include the [Date] field in your report's record source. You can then
sort/group on the expression:
=Month([Date])

BTW: Date is not a very good name for a field since Date is the name of a
function.

--
Duane Hookom
MS Access MVP
--

This is how I have it formated. HOw can I change this so it will sort by
month and in the correct monthly order?
DATE By Month: Format$([MAINTABLE].[DATE],'mmmm yyyy')
THanks,
Pixie

I have done the sorting but it still sorts alphnumeric and Febuary comes
before January and March comes after January. I want them to be January,
Feburary, March, etc....

 

[Guest]

Thank you very much that fixed the problem.
Pixie

Just include the [Date] field in your report's record source. You can then
sort/group on the expression:
=Month([Date])

BTW: Date is not a very good name for a field since Date is the name of a
function.

--
Duane Hookom
MS Access MVP
--

This is how I have it formated. HOw can I change this so it will sort by
month and in the correct monthly order?
DATE By Month: Format$([MAINTABLE].[DATE],'mmmm yyyy')
THanks,
Pixie

I have done the sorting but it still sorts alphnumeric and Febuary comes
before January and March comes after January. I want them to be January,
Feburary, March, etc....


:

If you use the Sort and Grouping in Access it should sort correctly no
matter
how you format the display.
Open the report in design view and click on menu VIEW - Sorting And
Grouping. Select your date field in the popup window.

:

Okay I have a subreport that is grouped by Month & year, but the
Month is
formated as January, 2006. It is sorting as alphnumeric. Is there
away to
change this sorting to follow January, Febuary, March instead of
Febuary,
January, March?

Thank you for any help in this matter,
Pixie