Determine keystage using date of birth.

[GA]

I need to find out what key stage pupils are at by reference to their
date of birth and it's got me stumped.

The key stages are in years running from 1st September to the
following 31st August and for example a child at key stage 1 will be 5
on or before the start of the current Sept-Aug period and will remain
in the key stage for 3 years.

I have no problem getting an age, year born, subtracting or adding x
number of years to the birth year but my problem starts here.

Question - If I have got a variable intYear = 1998 then how do I make
01/09 + intYear into #01/09/1998# so I can use it in my if statements.

If anybody already has a fuinction or code snippet that does this that
they would be willing to share I would be very grateful.

TIA
GA

 

[Baz]

I need to find out what key stage pupils are at by reference to their
date of birth and it's got me stumped.

The key stages are in years running from 1st September to the
following 31st August and for example a child at key stage 1 will be 5
on or before the start of the current Sept-Aug period and will remain
in the key stage for 3 years.

I have no problem getting an age, year born, subtracting or adding x
number of years to the birth year but my problem starts here.

Question - If I have got a variable intYear = 1998 then how do I make
01/09 + intYear into #01/09/1998# so I can use it in my if statements.

If anybody already has a fuinction or code snippet that does this that
they would be willing to share I would be very grateful.

TIA
GA

If you mean "If statements" in VBA code, then you want something like this:

Cdate("01/09/" & intYear)

The "#" delimiters are only relevant in SQL. But, if you want to create a
date literal for use in SQL, you will also need to Americanise the date
format (sad but true) so you will finish up with something like this:

"#09/01/" & intYear & "#"

 

[Douglas J Steele]

If you mean "If statements" in VBA code, then you want something like this:

Cdate("01/09/" & intYear)

The "#" delimiters are only relevant in SQL. But, if you want to create a
date literal for use in SQL, you will also need to Americanise the date
format (sad but true) so you will finish up with something like this:

"#09/01/" & intYear & "#"

CDate respects whatever the user has chosen as the Short Date format in
Regional Settings. CDate("01/09/" & intYear) will return a date of September
1st for the given year for users who have chosen dd/mm/yyyy, but will return
a date of January 9th for users with mm/dd/yyyy or (I believe: sorry, don't
have time to test at the moment!) yyyy-mm-dd.

Since you should not rely on user Regional Settings being something
specific, it's far better to use the DateSerial function:
DateSerial(intYear, 9, 1)

 

[GA]

Douglas J Steele wrote:

[snip]

Since you should not rely on user Regional Settings being something
specific, it's far better to use the DateSerial function:
DateSerial(intYear, 9, 1)

[snip]

Many thanks. Much appreciated.
GA