Date query - how do I find the last April? (1 Viewer)

[wilsation]

Hi, I have several queries based around the financial year so they mostly begin in April (01/04). The following code will count all rows in April of the current year, but if I run it in January it obviously returns zero.

Count(IIf([RTB1] Between "01/04/" & Year(Date()) And "30/04/" & Year(Date()),0))

I know I can add DATE()-1 to go back to the previous year but then if you run it in May it will return results for April the previous year.
So what I need is a way of going back to the last April regardless of the current month.

Thanks

 

[Ranman256]

Date math is done with DATEADD.
D=day
M=month
Yyyy= year

DAteAdd("yyyy",-1,"30/04/" & year(Date))

 

[Galaxiom]

DateSerial(Year(Date) + (Month(Date) < 4), 4, 1)

(Month(Date)<4) evaluates as -1 or 0 so if the month is less than April, one year is subtracted.

 

[wilsation]

Hi Ranman, so my code should look like this?

App1: Count(IIf([RTB1] Between DateAdd("yyyy",-1,"01/04/" & Year(Date)) And DateAdd("yyyy",-1,"30/04/" & Year(Date)),0))

When I add it to my query Access adds brackets to the Date like this...

App1: Count(IIf([RTB1] Between DateAdd("yyyy",-1,"01/04/" & Year([Date])) And DateAdd("yyyy",-1,"30/04/" & Year([Date])),0))

Which obviously doesn't work.

Thanks

 

[wilsation]

Got it sorted thanks, used this:

App1: Count(IIf([RTB1] Between DateAdd("yyyy",-1,"01/04/" & Year(Date())) And DateAdd("yyyy",-1,"30/04/" & Year(Date())),0))