calculating age

[suzzieo]

I have 2 fields in my table; "Birthdate" and "Age". I will be entering the birthdate but I want the age automatically calculated from the birthdate field. Can someone help me with the correct code? Thank you so much!!

 

[The_Doc_Man]

Look up DateDiff in the Help section. That function will do exactly what you want. Your inputs to it will be the birthdate and the Now() function (another Help lookup if you aren't familiar with it).

But now, take this criticism gently: You NEVER need to store age in your table if you have birthdate - because you can always compute it as noted above using DateDiff. So in a sense, it represents redundant data.

 

[ColinEssex]

Have a butchers at this thread Suzie. It gives 2 options (there are more if you search on Age Calculation)

Thanks to Ally and OldSoftBoss

http://www.access-programmers.co.uk...p?s=&threadid=35637&highlight=Age+Calculation

Col

 

[Mile-O]

And another

 

[Travis]

Datediff will give you the number of Years, but does have an issue. For instance someone who is born October 30, 1972 will show as being 31 years old if you use the formula Datediff("yyyy",#10/30/1972#,Date()).

In actuality they are only 30 (and 6 months). below are two formulas that could be used to accomidate this type of discrepency.

'This uses the datediff and a boolean expresion (Return is 0 for False and -1 for True) If the birthdate has not happened yet this year it subtracts a year from the calc.
Datediff("yyyy",#10/30/1972#,Date()) + (#10/30#>Date())


'This one uses the Integer division. It calculates the number of Months that has occured and divides by the number of months in the year.
Datediff("m",#10/30/1972#,Date())\12

 

[Rich@ITTC]

Why not use a module and then call upon it?

----------------------------------------------------------
Function Age(varDOB As Variant, Optional varAsOf As Variant) As Variant
'Purpose: Return the Age in years.
'Arguments: varDOB = Date Of Birth
' varAsOf = the date to calculate the age at, or today if missing.
'Return: Whole number of years.

On Error GoTo Err_Age

Dim dtDOB As Date
Dim dtAsOf As Date
Dim dtBDay As Date 'Birthday in the year of calculation.

Age = Null 'Initialize to Null

'Validate parameters
If IsDate(varDOB) Then
dtDOB = varDOB

If Not IsDate(varAsOf) Then 'Date to calculate age from.
dtAsOf = Date
Else
dtAsOf = varAsOf
End If

'Calculate only if it's after person was born.
If dtAsOf >= dtDOB Then
dtBDay = DateSerial(Year(dtAsOf), Month(dtDOB), Day(dtDOB))
Age = DateDiff("yyyy", dtDOB, dtAsOf) + (dtBDay > dtAsOf)
End If
End If

Exit_Age:
Exit Function

Err_Age:
MsgBox Err.Description & _
" - (Error No:" & Err.Number & ")"
Resume Exit_Age

End Function
-----------------------------------------------------------------------

In the form you can then call upon the Age function to calculate the age in years of someone based on their birthday

 

[R. Hicks]

The age should not be stored in your table ... unless you need to store the "Age at the Time of entry".

You can use this expression to correctly calculate the persons age"

DateDiff("yyyy", [DOB], Date()) + (Date() < DateSerial(Year(Date()), Month([DOB]), Day([DOB])))

HTH
RDH

 

[suzzieo]

thank all of you for responding to helping me calculate age - however! I set up the calculation in my query using

Age: DateDiff("yyyy", [DOB], Date()) + (Date() < DateSerial(Year(Date()), Month([DOB]), Day([DOB])))

and it works beautifully but how do I get that calculated age to appear on the form?

thanks

 

[Travis]

Set to control source of the Field on the form to the Field in the Query. (I'm assuming that the query will be the RecordSource for your form)