MS Access iff statement

  Gary Wood 17:18 30 May 06
Locked

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,

Gary

  Woolwell 18:53 30 May 06

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 06

Woolwell,

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.

Gary

  Main Access 20:34 30 May 06

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 06

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

Similarly,

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

(Month(Date())<=8),"Yes","No")

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

  Gary Wood 15:27 01 Jun 06

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.

How to get Windows 10 for free | How to install Windows 10: There is still a way to avoid paying…

1995-2015: How technology has changed the world in 20 years

Alex Chinneck’s giant ice cube Christmas tree at Kings Cross

Apple rumours & predictions 2017: The iPhone 8, new iPads, and everything else you should expect fr7…