Sort Dates by Months irrespective of Year

[xpnovice]

I am trying to compile a list of historical events by Month irrespective of
the year. I can format the dates to display only the month i.e. January etc
in my report, but the sort is still by the underlying year value, so I do
not get the desired list of events by month. I have tried various options
with formatting and grouping but no luck.
Can anyone help please.

Thanks

 

[Allen Browne]

In the Field row of your query, enter:
TheMonth: Month([EventDate])

In the Sorting'n'Grouping dialog of the report, you can then sort on this
field.

 

[xpnovice]

Thanks Allen,
I had tried a calculated query but it was simply to display the month. The
Month option is obvously what I should have been using.

I see this returns the numeric value for the month ie 4 = April, I can use a
long IIf statement in the report to change this numeric value to equate to
the month. However, is there a simpler way to do this?

Thanks
John

In the Field row of your query, enter:
TheMonth: Month([EventDate])

In the Sorting'n'Grouping dialog of the report, you can then sort on this
field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am trying to compile a list of historical events by Month irrespective
of the year. I can format the dates to display only the month i.e.
January etc in my report, but the sort is still by the underlying year
value, so I do not get the desired list of events by month. I have tried
various options with formatting and grouping but no luck.
Can anyone help please.

 

[Allen Browne]

In the more recent versions, you can put this in the ControlSource of a text
box:
=MonthName([TheMonth])

Older versions:
=Format(DateSerial(2001, [TheMonth], 1), "mmmm")

 

[xpnovice]

Thanks Allen that works fine.

John

In the more recent versions, you can put this in the ControlSource of a
text box:
=MonthName([TheMonth])

Older versions:
=Format(DateSerial(2001, [TheMonth], 1), "mmmm")

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen,
I had tried a calculated query but it was simply to display the month.
The Month option is obvously what I should have been using.

I see this returns the numeric value for the month ie 4 = April, I can
use a long IIf statement in the report to change this numeric value to
equate to the month. However, is there a simpler way to do this?

Thanks
John