MS Access iff statement

  Gary Wood 17:18 30 May 2006

A database I'm creating contains records of events within an academic year. There will be many events within the table, and I want to create a query to show me only those events in the current academic year.

I figure that I need to use some sort of iff statement to make this happen; something which says:

IF the current date is before 31-Aug-YYYY
THEN display records in the range 01-Sep-LastYear and 31-Aug-ThisYear
ELSE display records in the range 01-Sep-ThisYear and 31-Aug-NextYear.

I've had a go at writing this, but can't get my code to work. Can anyone help?

Thanks in advance,


  Woolwell 18:53 30 May 2006

It may be easier to create a parameter query and manually input the start date and end date of the range you want.
eg In Criteria enter Between [Type the beginning date:] And [Type the ending date:].

  Gary Wood 19:07 30 May 2006


Thank you for your reply.

It would certainly be easier to achieve this with parameter queries, but I need to use the forumla in several queries, each of which I will use several times a day. Since the process is always the same, I therefore need to automate it if possible.


  Main Access 20:34 30 May 2006

you could write a where statement that is saved,
and then write command buttons that will open each query with the where statment.
of course this would need a knowledge of vba

  Gary Wood 22:33 30 May 2006

I've got a bit further with my own attempt, but something interesting is happening. If I enter

Between DateSerial(Year(Date())-1,9,1) And DateSerial(Year(Date()),8,31)

then I get all the records dated between September 2005 and this August 2006 (i.e. exactly what I'm looking for, since today's date is less then 31 August).


Between DateSerial(Year(Date()),1,9) And DateSerial(Year(Date())+1,31,8)

gives me dates between 1 September 2006 and 31 August 2007 (i.e. what I would want to see if the current date were between 1 September and 31 December).

Further, the following code checks the current month number and returns "Yes" if it's before or equal to August (i.e. month number 8) and "No" if it's after August (i.e. month number after 8):


Interestingly, though, when I put all these parts together to give the following, the whole thing stops working:

IIf((Month(Date())<=8),([Table1].[Date]) Between DateSerial(Year(Date())-1,9,1) And DateSerial(Year(Date()),8,31),([Table1].[Date]) Between DateSerial(Year(Date()),1,9) And DateSerial(Year(Date())+1,31,8))

I don't get any error messages but when I save and run the query, I don't see any records at all.

Can anyone point out what is causing this code not to work?


  Gary Wood 15:27 01 Jun 2006

I've got a solution!

A member of another forum in which I asked about this issue has just posted the following code which, when entered into the Criteria box of the "Date" column of the query, correctly filters records from just the current academic year:

Between DateSerial(IIf(Month(Date())>8, Year(Date()), Year(Date())-1), 9, 1) And DateSerial(IIf(Month(Date())>8, Year(Date())+1, Year(Date())), 8, 31)

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

HTC U12 Plus review: Hands-on

See how VFX studio Rise created 'Jabariland', holograms & digital doubles in Marvel Studios' Black…

Best Android emulators for Mac

Comment importer des contacts d’un iPhone à un autre iPhone ?