automatically calculate DOB or age fields if not entered

[GeoffW]

I have a form where I ask for either Date of Birth, or age. I want to
automatically calculate and store the age field if the user enters a date of
birth, otherwise, I'll store the user-entered age value.

I can successfully use an expression to calculate the age value, my query is
1) how to store that back in the underlying table; and
2) how to allow both options - accept the user entered age, or calculate it
if the user enters a date of birth instead.

 

[Wayne-I-M]

IIf(Not
IsNull([Forms]![FormName]![AgeField]),[Forms]![FormName]![AgeField],(DateDiff("yyyy",[Forms]![FormName]![DateofBirthField],Date())-IIf(Format([Forms]![FormName]![DateofBirthField],"mmdd")>Format(Date(),"mmdd"),0,0)))

 

[Wayne-I-M]

On a form you could use something like

If (Not IsNull(Me.AgeField)) Then
Table!Age = Me.AgeField
End If
If (IsNull(Me.AgeField)) Then
Table!Age = DateDiff("yyyy", DateofBirthField, Date) -
IIf(Format(DateofBirthField, "mmdd") > Format(Date, "mmdd"), 1, 0)
End If

Hope this helps

 

[Douglas J. Steele]

You should never store Age, since it's constantly changing.

If the user inputs an age, you should calculate a guess of what the DOB is,
and store that instead.

 

[punitha]

hi,

i hv the saame need as GeoffW.
but where do i put the code, which event in the form?

thanx in advance.

 

[punitha]

as of now i'm hv this in Age control source:
=DateDiff("m";[DOB];[DateRec])\12 & " y " & DateDiff("m";[DOB];[DateRec])
Mod 12 & " m"

most of the time i dont get the DOB so i need to insert the Age instead.
how do i do tat?