Calculating year from range

[kiwiora]

Hi guys,

I have a database of invoices. What i'm stuck on is I need to create a total of just those invoices that fall in any given financial year.

Australian financial year runs from 01/07/year to 31/06/year (e.g 1 July 2004 to 31 June 2005).

I think my problem is text -v- date but can't seem to work it out.

I have a global field being the entry of the current financial year. so if( CurrentFinancialYear = Invoice Year; Fees; "")

But I can't even get InvoiceYear to be calc'd???

The calc of the InvoiceYear is:

Case(

InvoiceDate  ≥  "1/7/2004"  and InvoiceDate   ≤  "31/6/2005"; "2004"; 

InvoiceDate  ≥  "1/7/2005"  and InvoiceDate   ≤  "31/6/2006"; "2005"; 

InvoiceDate  ≥  "1/7/2006"  and InvoiceDate   ≤  "31/6/2007"; "2006"; 

InvoiceDate  ≥  "1/7/2007" and InvoiceDate   ≤  "31/6/2008"; "2007"; "2008")

Thanks in advance

[Søren Dyhr]

Hows about:

Year ( Date ( Month ( InvoiceDate )+6 ; Day ( InvoiceDate ) ; Year ( InvoiceDate ) ) )

--sd

[comment]

or:

Year ( InvoiceDate ) + ( Month ( InvoiceDate ) > 6 )

Note that "31/6/2005" is not a valid date - June has only 30 days.

[IdealData]

Year ( InvoiceDate ) - ( Month ( InvoiceDate ) < 7 )

I think.

[kiwiora]

so simple, so brilliant.. thanks guys you rock!!!

Year ( InvoiceDate ) - ( Month ( InvoiceDate ) < 7 ) was the winner to get the correct year

Note that "31/6/2005" is not a valid date - June has only 30 days.

doh.. slip of fingers :