ms access annual criteria

Question

I need to create a query criteria to get any date from the last 1 may of last year till today, example if I run the query now it should get the data from 1 may 2012 till today, if I run the query the next year on Feb 2013 then get the data from 1 May 2012 till feb 2013.

update

I have used the below as the [JOINED DATE] query criteria but it returns nothing, what is wrong with this?

IIf(Month([Data]![JOINED DATE])>=5,Between DateSerial(Year(Now()),5,1) And Now(),Between DateSerial(Year(Now())-1,5,1) And Now())

Matt Donnan Matt Donnan · Accepted Answer · 2012-05-21T12:18:36

Your syntax is incorrect, I do not advise including the "between" keyword in the IIF statement, you want your IIF to only return the date, something like:

SELECT *
FROM A
WHERE A.Date BETWEEN IIf(Month([Joined Date])>=5,DateSerial(Year(Date()),5,1),DateSerial(Year(Date())-1,5,1) AND Date()

Note: I have used Date() rather than Now() as Now() includes the timestamp which is not necessary in this case.

ms access annual criteria

1 Answers