Function for a form text box

[Rmaster2022]

I copied the following function from a website but edited it to reflect the name of the birth date field:

Public Function AgeYears(ByVal BirthDate As Date) As Integer
' Comments: Returns the age in years
' Params : BirthDate Date to check
' Returns : Number of years
' Source : Total Visual SourceBook
On Error GoTo PROC_ERR

Dim intYears As Integer

intYears = Year(Now) - Year(BirthDate)

If DateSerial(Year(Now), Month(BirthDate), Day(BirthDate)) > Now Then
' Subtract a year if birthday hasn't arrived this year
intYears = intYears - 1
End If

AgeYears = intYears

PROC_EXIT:
Exit Function

PROC_ERR:
MsgBox "Error: " & Err.Number & ". " & Err.Description, , "modDateTime.AgeYears"
Resume PROC_EXIT
End Function

On the form I have a field 'birthdate" and next to it another text box that will show the age. How do I reference this function in the form text box and/or the query so when one opens the form, the age appears?

 

[moke123]

In your forms query add a field alias and the function and bind the control to that field'

txtAge: AgeYears([Your birthday field])

 

[Rmaster2022]

I placed txtAge: AgeYears([BirthDate]) in the field name of the query but I get a message "Undefined function: 'AgeYears in expression' (Birthdate is the name of the field in my database.) There must be something wrong in the code. I know very little about coding, but I have copied code and have been successful. This is what I used. .

Public Function AgeYears(ByVal BirthDate As Date) As Integer
' Comments: Returns the age in years
' Params : BirthDate Date to check
' Returns : Number of years
' Source : Total Visual SourceBook
On Error GoTo PROC_ERR

Dim intYears As Integer

intYears = Year(Now) - Year(BirthDate)

If DateSerial(Year(Now), Month(BirthDate), Day(BirthDate)) > Now Then
' Subtract a year if birthday hasn't arrived this year
intYears = intYears - 1
End If

AgeYears = intYears

PROC_EXIT:
Exit Function

PROC_ERR:
MsgBox "Error: " & Err.Number & ". " & Err.Description, , "modDateTime.AgeYears"
Resume PROC_EXIT
End Function

Also, what code can I add that would return a "0" if the birthdate field is null,

 

[arnelgp]

copy your Code to a New Module (in VBA).
note that your Date of Birth field must not be blank, otherwise you will get an error messsage.

on a quey:

select *, AgeYears([DateOfBirthField]) As Age From yourTableName;

 

[moke123]

Also, what code can I add that would return a "0" if the birthdate field is null,

You wrap the birthdate field with NZ() and use the current date as the alternative value.

txtAge: AgeYears(Nz([BirthDate],Date()))

 

[Rmaster2022]

copy your Code to a New Module (in VBA).
note that your Date of Birth field must not be blank, otherwise you will get an error messsage.

on a quey:

select *, AgeYears([DateOfBirthField]) As Age From yourTableName;

That worked. Thank you.

 

[Rmaster2022]

You wrap the birthdate field with NZ() and use the current date as the alternative value.

txtAge: AgeYears(Nz([BirthDate],Date()))

Thank you. That works!

 

[moke123]

The bottom line is, if you pass in a null date, the Nz() changes it to 0 and you get back an age of something around 122 years because null got converted to 0 which was interpreted as 12/30/1899.

Pat I think your reading it wrong.

txtAge: AgeYears(Nz([BirthDate],Date()))

We're passing in Date() ,as in todays date, as the value if null, not 0. The function returns an integer as the age in years.