Formula to find dates before a certain date based on certain conditions

[Aaron Greatbanks]

Hello



I have a ICT exam on databases in January for A2 ICT. It is based on a sixth form college

In the scenario which we are given, they have informed us that we will need to "Automatically flag up students who have completed the first year"

I'm searching for a formula that will look for start dates of students before the 1st September but the year of the 1st September changes

I so far have used the following formula:

<DateSerial(Year(Date())-0,9,1)

This will look for students that enrolled before 01/09/2012 if i used it based on today. However, when 2013 comes, this formula will find people that enroled before 01/09/2013 which isn't what we'd be looking for

We would need this formula

<DateSerial(Year(Date())-1,9,1)

However, i need to find a formula that automatically updates so that it finds people who enroled before the last 1st September

So if say i was running this query of 9th March 2013, i'd be looking for people who enrolled before 01/09/2012.

Can you think of any solutions?

[pbaldy]

This give you any ideas?

Year(Date()) - IIf(Month(Date()) < 9, 1, 0)