Format date for group by function

22strider

Active Member
Joined
Jun 11, 2007
Messages
299
Hello Friends,


For my report I have to group records by Month/Year. The data has date in the format of date+month+year+time; I was able to reformat to Month-Year format. But the problem is that the reformat is just a view. When I try to group based on the reformatted data Access considers date+month+year+time and does not group based on Month-Year.

Second part of the requirement is being able to sort data by month-date.

Could you please tell what my options are.

Regards,
Rajesh
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,799
Office Version
365
Platform
Windows
You can use the MONTH and YEAR functions to pull the MONTH & YEAR out in a calculated fields and use those for your Grouping and Sorting Purposes.
 

22strider

Active Member
Joined
Jun 11, 2007
Messages
299
Wanted to add that I am using MS Access 2013

Hello Friends,


For my report I have to group records by Month/Year. The data has date in the format of date+month+year+time; I was able to reformat to Month-Year format. But the problem is that the reformat is just a view. When I try to group based on the reformatted data Access considers date+month+year+time and does not group based on Month-Year.

Second part of the requirement is being able to sort data by month-date.

Could you please tell what my options are.

Regards,
Rajesh[/QUOTE]
 

22strider

Active Member
Joined
Jun 11, 2007
Messages
299
Hello Joe4,

Thanks for your replay.
I did try that. The problem was with sorting. The date field was getting sorted alphabetically rather than in date chronological order.

Regards,
Rajesh
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,799
Office Version
365
Platform
Windows
Can you post the Calculated field calculation you created?
 

22strider

Active Member
Joined
Jun 11, 2007
Messages
299
Hello Joe4,

Following is my calculation:

DatePart("M", [Effectivity Date])&"/"&DatePart("yyyy", [Effectivity Date]) AS [Calculated Date]

Upon sorting results based upon above given script '4/2015' comes before '9/2014'
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,799
Office Version
365
Platform
Windows
OK. By using the DatePart functions and concatenating them together like that, you are creating a string (it is not a date value). As such, it is sorted alphabetically, and since "4" comes before "9", your data is sorted like that.

There are a few ways around this. Here are two:

Method 1:
Use a calculated field that lists year before month, i.e.
MyDate: Format([Effectivity Date],"yyyy-mm")
and use that. This returns a string, but since Year is listed before Month, and all Months are two digits, it will sort/group like you want.

Method 2:
Use two calculated fields, one for MONTH and one for YEAR that returns both values as numbers, i.e.
MyYear: Year([Effectivity Date])
MyMonth: Month([Effectivity Date])
then Group on BOTH of these fields. In sorting, sort first by MyYear, and second by MyMonth.

Keep in mind that if you want to display your date on your Report in "m/yyyy" format, you can still use your original calculated field, you just also need to include one of these other two options in you query, and sort/group based on those fields instead of your original calculation.
 

22strider

Active Member
Joined
Jun 11, 2007
Messages
299
thank you very much. I am sure both methods you suggested would work. I tried Method1 and it worked; going with Method1.

Thanks again,
Rajesh
 

Forum statistics

Threads
1,085,834
Messages
5,386,226
Members
401,986
Latest member
crt54

Some videos you may like

This Week's Hot Topics

Top