sum by date range.....

[robmason10]

Compiling a time/cost report which I need to relate to a financial year whicg runs 01-Oct to 30-Sept. I would like to return a sum of {LedgerEntries} based on currentdate.

i.e.

If currentdate is between 01-oct-2002 and 30-sep-2003 then select from tblLedger where {ledgerEntryDate} is between 01-oct-2002 and 30-sep-2003

Seems obvious on screen but can't get my head around it. Any suggestions?

 

[lbass]

If Month(CurrentDate) in [10 to 12] then
{Yourdatefield} in Date(Year(CurrentDate),10,01) to Date(Year(CurrentDate)+1, 09, 30) else
if Month(CurrentDate) in [1 to 9] then
{Yourdatefield} in Date(Year(CurrentDate)-1,10,01) to Date(Year(CurrentDate), 09, 30)

Or you could create a parameter {?FiscalYear}, and assuming you label your fiscal year by the year ending, your formula could then be:

{Yourdatefield} in Date({?FiscalYear}-1,10,01) to Date({?FiscalYear}, 09, 30)

-LB