Between two Months

[Sging1]

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.

 

[Mile-O]

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.

 

[Sging1]

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.

 

[Mile-O]

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)))

 

[Mile-O]

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.

 

[Sging1]

Excellent thanks a lot, works a treat.