using date of birth to compile age groups

[onlyjohn]

HI everyone, and thankyou for reading this.

I am the secretary of an athletics club and have the task of using membership data to compile groups of athletes.
One of the fields that I have available is the date of birth of every athlete. It is entered in the form dd/mm/yyyy.
I would like to be able to query the database and display the age of each athlete.

I would also like to be able to query the database so that I can compile groups of athletes grouped by their ages as at 01/09/yyyy
These groups would be: Under 11 years,
over 10 years but under 13 years,
over 12 years but under 15 years,
over 14 years but under 17 years under 20 years.
over 19 years.
I have some understanding of using SQL view in Access but my overall knowledge must be viewed as "novice".
If you are able to help me with this problem I would be extremely grateful (as would our club), and I would also be obliged if you could keep instructions simple.
Many thanks
John

 

[Rickster57]

Age

In your query that your form uses as it's Data Source place the following field:

age: Int((DateDiff("d",[Birthdate],Date()))/365.25)

I have used this in a database that does Race Results (such as 10k, 5k, etc.)
for years where the Date() would be set to race date and has always worked.

Hope this helps

 

[ScottGem]

A more accurate way of determining age is:

Age=DateDiff("yyyy", [Bdate], Now())+ _
Int( Format(now(), "mmdd") < Format( [Bdate], "mmdd") )

If you need to use a different date then the current one substitute it for Now().

But that only solves part of your problem. What you need is a function that returns a grouping. Something like this:

Public Function AgeGroup(dteDOB As Date) as String

Dim intAge As Integer

intAge = DateDiff("yyyy", [Bdate], Now())+ _
Int( Format(now(), "mmdd") < Format( [Bdate], "mmdd") )

SELECT Case intAge

Case < 11
AgeGroup = "under 11"

Case >10 AND < 13
AgeGroup = "Between 11 & 12"

etc.

End Select
End Function

You can then add a column to your query:

AgeGrps: AgeGroup([DOB])

and use that to sort or group by