Between two Months

  • Thread starter Thread starter Sging1
  • Start date Start date
S

Sging1

Guest
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.
 
Sging1 said:
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(DateAdd("yyyy",-1,Date())),4,1),DateSerial(Year(Date()),4,1)) And IIf(Date()<=DateSerial(Year(Date()),3,31),DateSerial(Year(Date()),3,31),DateAdd("yyyy",1,DateSerial(Year(Date()),3,31)))
 
In fact, disregard that. Too Complex:

Open a module and past this code:

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.
 

Users who are viewing this thread

Back
Top Bottom