Format date for group by function

[22strider]

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]

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]

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]

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]

Can you post the Calculated field calculation you created?

 

[22strider]

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]

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]

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