Format month and year

[icsupt]

I have a report that has many different dates in it. However I want to group by month and year, regardless of the day. In other words, if it has 01/07/2005, I want this to appear in the group for 01/2005. If it has 02/10/2005, I want this to appear in the group for 02/2005.

I typed this in format mmyyyy, but it is separating it according to the actual day.

Is there a way to sort by month and year, even though the user has input different days of the month?

Thanks in advance.

 

[oticonaus]

Not sure how your record source is set up, but the following sql query will change a range of dates into the format "m/yyyy". Hope that helps

SELECT DatePart("m",[Date]) & "/" & DatePart("yyyy",[Date]) AS MonthYear
FROM WorkingData;

 

[ShellyL]

In your report, do you have your date field as one of the grouping levels, and the group properties as "group on month? That usually works for me.

And then I ususally have the date field as a group header, and add a text box control to display the month:
=Format$([YourDateFieldName],"mmmm yyyy",0,0)