Group by Month-Year 2

[lewes]

Hi

I'm using CR9. I have records which have a created date in format dd-mm-yyyy. I want to be able to group the records by month and year ie. mm-yyyy. I am able to extract the month and year as a string from the date field but when sorting or grouping it displays in alphabetical order instead of date order. For example it goes Apr-05,Apr-06,Apr-07 etc. instead of Apr-05,May-05,June-05.

Does anyone know how to do this so they can dsiplay in date order? Thanks

 

[Madawc]

Try grouping by a formula field with something like

Year({your.date}) & "/" & Month({your.date})

You can also use ToText to display the date in some other format for each group.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lewes]

Thanks for this Madawc. It now sorts the Year/Month in date order. However instead of displaying 2005/10 it appears as 2005.00/10.00 Do you know how I can remove the decimal places? I tried it with Cstr(date,0) but this then stops it sorting in date order and goes back to alphabetical.

Thanks

 

[lbass]

Why not just insert a group #1 on {table.date} on change of year, and then a second time on change of month. You can then place the date field in the group #2 header and format it as you wish it to display. Suppress the group #1 header.

-LB

 

[lewes]

Thank you Ibass. I will try this but I also want the year and month to appear on the x axis of a chart. Up until now it has either appeared as 2009.00/6.00 or I can remove the .00 using Cstr and they display alphabetically - 2009/1, 2009/10 etc.

Here's hoping it will work this time!!

 

[lbass]

You should have mentioned the chart in your initial post. Try the numeric axis chart using one of the date options.

Also note that if you format dates converted by strings using YYYY/MM with two characters for month, they will sort correctly.

-LB