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!

 

[Pat Hartman]

1. You did not need to change the function at all. The name used in the function has nothing to do with the name of your column. The data is passed from your form to the function by reference, not by name. So "Public Function AgeYears(ByVal BirthDate As Date) As Integer" Establishes "BirthDate" as the name that will be used WITHIN the function but you can use ANY name for the date field that is passed to the function. For example Me.SomeField = AgeYears(SomeDateName) ===== works just fine. SomeDateName is the first argument so it aligns with the first lectical which is BirthDate.
2. I would not use the Nz() suggestion because the Date data type is NOT a string. It is a double precision number and it uses the concept of an origin date. So zero is actually the value for 12/30/1899. -1 = 12/29/1899 and +1 = 1/1/1900. Excel and SQL Server use the same concept but not the same origin value. You don't need to worry about that, they all know how to talk to each other. 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. THEREFORE, you need to trap the error inside the function by making the date field optional. Then the code checks for a value and if it is null, returns "something" which leads us to what should it return OR should you also change the return value of the function from Integer to something else. And that answer depends on what you're going to do with the result. I'm talking as stream of conscious because you need to work out the ramifications of your decisions. If you are using the function in a form, then you can validate the date BEFORE you call the function and decide what to return or to reject the entry and refuse to save the data. if you are calling the function from a query as was suggested, you have no way of validating on the way in so you need to trap the error once you are in the function and figure out what it makes sense to return so you can identify an error. I don't know what you want. You may not know what you want. But now you have things to think about so you can decide what makes sense and we can help you to implement.

 

[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.

 

[Pat Hartman]

Thanks moke, I didn't look at the example, I was looking at the request and got confused

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