Age groups from date of birth

[Stefan53nz]

Hi

I've a club database the records score for juniors on age groups that are set as of the 1st January each year. I would like to set this automatically from the date of birth. I have found some code that is very close to what I need apart from the fact it groups on the exact DOB resulting in changes to age group mid year.

Could someone advise changes require to the code below.

'----------------------------Code Start--------------------------------
Public Function AgeGroup(dtmBirthDate As Date) As String

Dim intAge As Integer

'Age Calculation
intAge = DateDiff("yyyy", [dtmBirthDate], Now()) + _
Int(Format(Now(), "yyyy") < Format([dtmBirthDate], "yyyy"))

Select Case intAge

'For each Age range, write out Age Group (used in qry)
Case 0 To 10
AgeGroup = "Kiwi"
Case 11 To 13
AgeGroup = "Cub"
Case 14 To 15
AgeGroup = "Intermediate"
Case 16 To 17
AgeGroup = "Cadet"
Case 18 To 20
AgeGroup = "Junior"
Case Is > 20
AgeGroup = "Senior"
End Select

End Function
'----------------------------Code End-----------------------------------

Thanks
Stefan

 

[John Big Booty]

Try;

Public Function AgeGroup(dtmBirthDate As Date) As String

Dim intAge As Integer

'Age Calculation
intAge = Int(DateDiff("d", [dtmBirthDate], DateSerial(DatePart("yyyy,Date()), 1, 1))/365.25)

Select Case intAge

'For each Age range, write out Age Group (used in qry)
...

End Function

 

[Stefan53nz]

Hi John

Tried your code but got an error. Played around with both the original & the code you supplied & can up with this code which seems to work. If there are any fundamental errors which could cause problems down the line please advise.

'----------------------------Code Start--------------------------------
Public Function AgeGroup(dtmBirthDate As Date) As String

Dim intAge As Integer

'Age Calculation
intAge = DateDiff("yyyy", [dtmBirthDate], Now(), 1, 1) + _
Int(Format(Now()) < Format([dtmBirthDate], "YYYY"))

Select Case intAge

'For each Age range, write out Age Group (used in qry)
Case 0 To 10
AgeGroup = "Kiwi"
Case 11 To 13
AgeGroup = "Cub"
Case 14 To 15
AgeGroup = "Intermediate"
Case 16 To 17
AgeGroup = "Cadet"
Case 18 To 20
AgeGroup = "Junior"
Case Is > 20
AgeGroup = "Senior"
End Select

End Function
'----------------------------Code End-----------------------------------


Thanks
Stefan

 

[RainLover]

Stefan53nz

If I read this right you can find the Age as of 1 Jan. At least you should be able to because the answer is in the other Thread that you posted in. BTW If you put a Link in that other thread to here then people can come here and read the whole story.

Anyway, write your query that calculates the age and save it as myDateSUB

The create a new query from the Sub Query but do not include any field that you do not wish to Group on. Namely DOB.

I have not tested this but hopefully it gives you what you want.

PS This can also be written in VBA if you need to.

Link to other thread.http://www.access-programmers.co.uk/forums/showthread.php?p=1259667#post1259667

 

[Brianwarnock]

I hope that John Big Booty does not use /365.25 in age calculations as that will give problems around birthdates.

As for this

intAge = DateDiff("yyyy", [dtmBirthDate], Now(), 1, 1) + _
Int(Format(Now()) < Format([dtmBirthDate], "YYYY"))

the comparison will always be true in the USA as it compares a text string starting with the month ie 01 to 12 with a text year starting 19xx or 20xx, of course with non US date strings we are starting with days so the result will be variable , but equally making no sense.

Age on Jan 1st is

agej1: DateDiff("yyyy",[dob],Date())+Int(Format([dob],"mmdd")<>"0101")

It is basically Datediff -1 except when the birthday is jan 1st

Brian