I know this must be really easy, but I'm going to ask anyway.

I want to return results bnetween 1st April and March end only. I don't want to include a particular year.

Originally posted by Sging1
I want to return results between 1st April and March end only. I don't want to include a particular year.

Can you clarify that further? 1st April to March end reads to me as if it were a whole year ann you don't want a particulat year so that would imply to me you want all records.

Sorry, its for a sickness staff database and I only want records for the financial year we are actually in. I have set up queries for the total sickness but for this one I will need just records from the 1st April to current date.

Then when we roll over to next April 1st it will reset itself.

Thanks for getting back so quick.

This should do it for you as a criteria - it shouldn't need changed ever again.

Between IIf(Date()<DateSerial(Year(Date()),4,1),DateSerial(Year(DateA dd("yyyy",-1,Date())),4,1),DateSerial(Year(Date()),4,1)) And IIf(Date()<=DateSerial(Year(Date()),3,31),DateSerial(Year(Dat e()),3,31),DateAdd("yyyy",1,DateSerial(Year(Date()),3,31)))

In fact, disregard that. Too Complex:

Open a module and past this code:

Public Function GetDates(boo As Boolean) As Date

Dim dteTemp As Date
dteTemp = DateSerial(Year(Date), 4, 1)
If boo = True Then
If Date <= dteTemp Then
GetDates = DateAdd("yyyy", -1, dteTemp)
Else
GetDates = dteTemp
End If
Else
dteTemp = DateAdd("d", -1, dteTemp)
If Date <= dteTemp Then
GetDates = dteTemp
Else
GetDates = DateAdd("yyyy", 1, dteTemp)
End If
End If

End Function



In the criteria for your query put:

Between GetDates(-1) And GetDates(0)


This will never need amended as it automatically calculates the financial year.

Excellent thanks a lot, works a treat.