Sort by month and year for report

[Slater]

Hi All,

I've got a set of invoices that i want to sort by the month and year , which i'd then show in a sub-summary report. That report would produce a list something like:

2007_01

2007_03

2007_08

2008_01

2008_02

2008_03

I'm sure there's an easy way to do it, though i've been searching and i couldn't find anything.

I have tried

Date_to_Sort_By = Year (Job_Date) & "_" & Month (Job_Date)

but the result for Jan is 2008_1 rather than 2008_01). This then doesn't sort properly because of Oct, Nov and Dec (ie 2008_10).

Many thanks if you can point me in the right direction,

Slater

ps am i right in thinking that i can't do anything to get FM to export the above list but with a 0 for months that don't have a value? ie

2007_01 £50

2007_03 £60

becomes

2007_01 £50

2007_02 £0

2007_03 £60

[RalphL]

I often use 100 * Year + month to give a suitable sort by year and month, but is this what you really want. Maybe you sort by year and then by month.

[LaRetta]

I suggest that you create a date calculation which turns all dates into the generic 'first day' of the their month. Then you can sort by this date calculation and it will always sort properly. In addition, you can then display only the month/year on your leading part (use layout date format). There are many other reasons a calculation like this comes in very handy, including relationship filtering etc.

yourDate - Day ( yourDate ) + 1

This standardizes all date records. It's a technique learned from Comment.

[Slater]

I often use 100 * Year + month to give a suitable sort by year and month, but is this what you really want. Maybe you sort by year and then by month.

Great idea!

I used

If ( Month (Date) <10 ; "0" & Month (Date) ; Month (Date) )

Thanks!

[Slater]

yourDate - Day ( yourDate ) + 1

Oh, and that is just sooo cool! Thanks,

Slater