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.
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.
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.
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") ?
I used =Format([MonthSort],"mmmm") on the form control and i get #error
Should I format in the query?
It should work in either, as long as that field is part of the form's source.
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.
As long as you realize you can no longer use that field for sorting.
back to the drawing board
Leave it as a number in the query, and add a textbox with the format function on the report.
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.....
Can you post the db itself?
sry, its too large
What happens if you put in a textbox bound to the MonthSort in the same section? Is it correct?
Did that. The one bound to the query is correct.
see attached