by year by month

[nkuebelbeck]

I have a date field that i would like to sort the record by. I want to sort by the year of that date and then by month of that year.

Any help would be much appreciated.

[pbaldy]

Does simply sorting on that date field not produce the desired result? If not, then in the query underlying the report, add two calculated fields:

YearSort: Year(YourDateField)
MonthSort: Month(YourDateField)

and sort on those two.

[nkuebelbeck]

I got this short after I posted. sometimes it just helps to spell it out. Do you know how i convert the month # to a string(so 3=March)

maybe format([monthsort],"mmmm") ?

[pbaldy]

The Format() function, yes.

[nkuebelbeck]

I used =Format([MonthSort],"mmmm") on the form control and i get #error

Should I format in the query?

[pbaldy]

It should work in either, as long as that field is part of the form's source.

[nkuebelbeck]

It would not work in the form control. I did have it as the control source...

MonthSort: Format([Entry Date],"mmmm")

That did the trick in the query.

[pbaldy]

As long as you realize you can no longer use that field for sorting.

[nkuebelbeck]

back to the drawing board

[pbaldy]

Leave it as a number in the query, and add a textbox with the format function on the report.

[nkuebelbeck]

I have this in the text box

=Format([MonthSort],"mmmm")

The text box is in the group header, now it shows up as january every time.....

[pbaldy]

Can you post the db itself?

[nkuebelbeck]

sry, its too large

[pbaldy]

What happens if you put in a textbox bound to the MonthSort in the same section? Is it correct?

[nkuebelbeck]

Did that. The one bound to the query is correct.

see attached