Surface Pro (2017) vs Surface Pro 4
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,
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.
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
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?
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.