Date query sometimes across years (1 Viewer)

[Marlene]

I need to return dates that occurred between today and the previous 1 June (the start of the financial year). Can anyone help as I seem to be spending lots of time and getting nowhere.

 

[plog]

I need to return dates that occurred between today and the previous 1 June (the start of the financial year)

Huh? I got the today part, but I can't really parse the other date you are referencing. Is it 6/1 of the previous year? If so this is the criteria:

<=Date AND >=6/1/2013

If not, please better explain your situation.

 

[Marlene]

Sorry I didn't explain myself clearly. There are many records over several years. I need to return all records with a date from the current day back to the previous June 1st, which may be in the current year if we are in October, or the previous year if we are in March. I don't want to specify a precise date ( June 1 2013) but automatically return dates back to whatever year the previous nearest June 1 is in.
So if today is in Oct 1 2014 then return records back to June 1 2014. If today is in March 2014 then return records back to June 1 2013. It needs to be dynamic so that it works next year as well

 

[Brianwarnock]

Ok I cannot test this but create a new col in the query

Startdate: IIF(Month(Date())<6,Dateserial(year(Date())-1,6,1),Dateserial(Year(Date()),6,1)

And in the criteria for this col
<=returndate
This assumes that you cannot have a returndate greater than today

Brian

 

[Marlene]

Absolutely perfect. Thank you very much.