sorting dates in subsummaries??

[Serenity]

Hi,

I have a table of line items (invoices). What I want to do is produce a report with the items sorted in (amongst other things) date order.

I wanted to create a subsummary of the month so I created a field which was month(date) Obviously this is based on the number of the month, rather than the name so as to sort correctly This works a treat. The problem is if there is April 2004 and April 2005, it doesn't differentiate.

Obviously I can't just add the year to the month because if there is for example August 2004, and January 2005, as a calc number august 2004 (2012) is larger than january 2005 (2006).

So, the question.

How can I sort my report into group my line items by month, taking into account the year as well???

I attach an example file.

Thank you in advance.

example.zip

[Ender]

Add a Year of Date field and sort by that and your Month field.

Alternatively, you could use one calc for the Year_Month, by padding the Month with a zero for months 1-9:

Year_Month of Date (calculation, text result) = Year ( Date ) & " " & right ( "00" & Month(Date) ; 2 )

[LaRetta]

Hi Serenity,

Create one calculation (result is date) dateField - Day ( dateField ) + 1

... where dateField is your lineitem date you are sorting by. Use THIS date to sort. It will convert all LineItem dates to the first day of the month. This makes them 'generic' on the day. They sort perfectly by month AND year. There are added benefits as well:

It solves all your date display problems, ie, change in Field Format it's date for it to display only the Month or Month - Year or whatever you wish. You will find yourself using this field constantly - particularly in LineItems. It becomes a perfect 'Month' joiner for relationships. Learned from Comment!

I used to find myself creating all kinds of calcs - a Year calc and a Month calc and concatenating them for other needs (Fiscal). But by creating one generic date, your sorts, finds and relationships can ALL benefit from the one calc. Wow! You're fast Mike! I'll leave mine as another idea anyway.

LaRetta

[Serenity]

thank you both. Great trick LaRetta!!