Pete The Biker

I hope someone can help with what seems like it ought to be a simple problem, but is baffling me.

I am writing a database for a sports club, where performance times are recorded for each competitor. I need to be able to group the results by the age of competitors on a given day, e.g. all those who are aged 15 on 10 March 2004.

I can do this without any problems at the year end (by using the 'year' function on the selected date, and then subtracting the year of the date of birth).

I can also do it on any given date by calculating the number of days between the selected date and the date of birth and dividing by 365. The only problem with this is that the calculation gets caught out by leap years.

Can anyone suggest how to get exactly the right answer?

Many thanks in advance.



Pete The Biker

FrenchBoy

Use the following expression:

DateDiff("yyyy",[dob],Date())

where [dob]= the date of birth

Date() is a function that returns the current date


DateDiff() is a function to calculate the difference between 2 dates.

"yyyy" = the required interval (years).


There are loads of great inbuilt date/time functions, just do a search in help.

Pete The Biker

Frenchboy

Great - many thanks for that - it's not quite the right answer, but it got me on the right trail on Microsoft's site:-
http://msdn.microsoft.com/library/de...lapsedtime.asp

Here is the text from it:-

You can use the DateAdd and DateDiff functions to calculate the time that has elapsed between two dates, and then, with a little additional work, present that time in the desired format. For example, the following procedure calculates a person's age in years, taking into account whether his or her birthday has already occurred in the current year.

Using the DateDiff function to determine the number of years between today and a birthdate doesn't always give a valid result because the DateDiff function rounds to the next year. If a person's birthday hasn't yet occurred, using the DateDiff function will make the person one year older than he or she actually is.

To remedy this situation, the procedure checks to see whether the birthday has already occurred this year, and if it hasn't, it subtracts 1 to return the correct age.

Function CalcAge(dteBirthdate As Date) As Long

Dim lngAge As Long

' Make sure passed-in value is a date.
If Not IsDate(dteBirthdate) Then
dteBirthdate = Date
End If

' Make sure birthdate is not in the future.
' If it is, use today's date.
If dteBirthdate > Date Then
dteBirthdate = Date
End If

' Calculate the difference in years between today and birthdate.
lngAge = DateDiff("yyyy", dteBirthdate, Date)
' If birthdate has not occurred this year, subtract 1 from age.
If DateSerial(Year(Date), Month(dteBirthdate), Day(dteBirthdate)) > Date Then
lngAge = lngAge - 1
End If
CalcAge = lngAge
End Function


All thanks to Frenchboy - the power of Scoobynet rules again!!!


Pete The Biker