Age Calculations

[lindaclark30]

I have the undernoted code calculating someone's age in a query. Thi
is based on their age as at Now() however I need it to calculate thei
age as at a certain date (ie the closing date for entries to
competition) can anyone tell me the code i need to replace this -
have tried replacing Now() with the closing date ie 9/4/04 but thi
doesn't work.

Years: IIf(Month(Int(Now()))>Month([Allinfo].[Dob]) O
(Month(Int(Now()))=Month([Allinfo].[Dob]) An
Day(Int(Now()))>Day([Allinfo].[Dob])),Year(Int(Now()))-Year([Allinfo].[Dob]),Year(Int(Now()))-Year([Allinfo].[Dob])-1)

This is probably easy to someone who understands the coding but I a
totally at a loss.

Thanks in advanc

 

[Duane Hookom]

Check out the function(s) at
http://www.mvps.org/access/datetime/date0001.htm. The best idea is to create
a user defined function:

Function Age(Bdate, DateToday) As Integer
' Returns the Age in years between 2 dates
' Doesn't handle negative date ranges i.e. Bdate > DateToday

If Month(DateToday) < Month(Bdate) Or (Month(DateToday) = _
Month(Bdate) And Day(DateToday) < Day(Bdate)) Then
Age = Year(DateToday) - Year(Bdate) - 1
Else
Age = Year(DateToday) - Year(Bdate)
End If
End Function

To do this:
-Create a new blank module
-copy the above code from "Function...." to "End Function"
-paste into the module window
-exit and save the module with a name like "modDateCalcs"
You can then use the function in your query like
Years:Age([DOB],#9/4/2004#)

 

[lindaclark30]

thank you, have now got it working.

Regards