Date criteria in query 1

[doctorswamp]

Hi all

I’m battling with date queries to extract data from a particular date each year.

First query needs to return data for financial year, between 1 April of one year and 31 March of following year, whatever the current financial year is.

So I’d like not to have to enter a specific date range such as ‘between 1 April 2005 and 31 Marsh 2006’, as this would mean having to re-enter the calendar year each new financial year.

I’d like a second simpler query to just return data for any month in the current financial year, ie entering 5 gives May results (in one calendar year), 3 gives March result (in next calendar year).

Help much appreciated as ever

 

[r937]

first query:

select ...
  from ...
 where datadate  
    between DateSerial(Year(Date())
                - Iif(Month(Date()) < 4,1,0)
                      ,04,01) 
        and DateSerial(Year(Date())
                + Iif(Month(Date()) < 4,0,1)
                      ,03,31)

r937.com | rudy.ca

 

[doctorswamp]

Thanks r937, that's just perfect.

Much appreciated.