Finding dates before a certain date but the formula changaes each year

Aaron Greatbanks

Registered User.
Local time
Today, 23:53
Joined
Dec 22, 2012
Messages
11
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?
 
As answered elsewhere

This give you any ideas?

Year(Date()) - IIf(Month(Date()) < 9, 1, 0)
 
Analyze it. What part don't you understand?
 
As answered elsewhere

This give you any ideas?

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

I've done it thankyou

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

can i ask you one last question?

I need to validate a date field so that the date entered is in the current academic year from 1st setember to the 31st July how would you do this so that the year on the date is always correct

like this year the validation would be

>=01/09/2012 and <31/07/2013.

How would you do this so that the years are always correct?

I got:

>DateSerial(Year(Date()),9,1) And <DateSerial(Year(Date())+1,7,31)

but if the date was 01/01/2013 this would go from 01/09/2013 to 07/07/2014 which isn't correct how would you do it so the years are correct
 
I would use the same concept to conditionally adjust the year based on the current month.
 
I would use the same concept to conditionally adjust the year based on the current month.

A huge problem has arisen that will massively disrupt the progress i make

I need to add new units for the courses via a form, with the unit being uniquely identified using a Unit Code

The Unit Code consists of the first 3 letters of the Subject Code, the first 2 letters of the Description of the unit and a number.

Eg Subject ID : ARA001 and Description: Culture will have a Unit code of ARACU1

I'm trying to find a way to automatically create the unit code on the form.

The first two bits have been solved fine,however, i have had difficulty of putting the number of the end.

It seems easy in practice as i need to find a Unit Code like the one i have on the form but find the maximum number of a identical code and add one to it

I thought if i did

Like "[Forms]![frmaddnewunit]![txtUnitCode]*" as the criteria on a query with the field being Unit Code from the Unit table it would find data like the one on the form but it doensn't. I thought i would use the data from the query and use Max(Right([UnitCode],1))+1 to get the number and add this to the code in the unit

This formula on the unit code text box on the form solves the first two bits of the problem

=Left([cboSubjectID],3) & UCase(Left([txtDescription],2))

but i need to find a way to find unit codes thsat exist similar to the ones that are created and then find the maximum number and add one to it so i have a valid unit code

e.g

if on the form i select Subject ID ARA001 and Description Culture

The unit code will display ARACU.

I then need to find unit codes similar to this (There is one that already exists ARACU1) and find the max number on the end and add one to it

So ideally the code that will be automatically generated in this case would be ARACU2

How do i do this?

Thanks
 
Typically with a DMax() function that finds the highest value with a criteria matching the first 5 digits. Usually an Nz() function is added to handle the first value for a given code. An example is at the bottom of this:

http://www.baldyweb.com/CustomAutonumber.htm

obviously your situation is a little different.
 
As answered elsewhere

This give you any ideas?

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

I just altered and update qeury so that it's a wuery based on a table and a qeury but when i run it is comes up with Operation Must Use An Updateable Query

This is the SQL Code:

UPDATE tblStudent INNER JOIN [qry=3ForCrossTableQuery] ON tblStudent.[Student ID] = [qry=3ForCrossTableQuery].[Student ID] SET tblStudent.WWOCompleted = True
WHERE ((([qry=3ForCrossTableQuery].[Total Number Of Unit])=3));

How do i resolve this?
 

Users who are viewing this thread

Back
Top Bottom