Age

  • Thread starter Thread starter Citipool
  • Start date Start date
C

Citipool

Hi there, I am using below code to calculate a contact age in my database. It
was working just fine yesterday. However, when I logged in today it's showing
an error inside the age txt box. Could someone please tell me what am I doing
wrong? Thanks alot.
'FUNCTION NAME: Age()
'
' PURPOSE:
' Calculates age in years from a specified date to today's date.
'
' INPUT PARAMETERS:
' varDateofBirth: a birth date.
'
' RETURN
' Age in years.
'
'*************************************************************
Function Age(varDateofBirth As Variant) As Integer
Dim varAge As Variant


If IsNull(varDateofBirth) Then Age = 0: Exit Function

varAge = DateDiff("yyyy", varDateofBirth, Now)
If Date < DateSerial(Year(Now), Month(varDateofBirth), _
Day(varDateofBirth)) Then
varAge = varAge - 1
End If
Age = CInt(varAge)
End Function***
 
Anytime that I see a problem with something that uses the Date on Now
functions, I have a knee jerk reaction to check for broken references.

Go to the VBA window and to Tools, References. Anything look broken there?

While in the VBA window, go to the Immediate panel and type in:

Debug.Print Date()

and hit enter. Any error? If no try:

Debug.Print Now()

If that doesn't show a problem, go up to Debug and Compile the database. See
if anything stops it from compiling.
 
Thank you, Jerry. I did the debugs both for Date and Now, it showed the
correct date/time. I also tried the combile DB, it didn't get stopped. My
form is still showing (#error) in Age txtbx. Since I am not getting any
messages for broken references either, what'd you recommend my next option? I
apperciate your help.
 
When something has been working and start to fail, but the code compiles I
tend to think that something is wrong with the input to function.

You don't check to make sure that varDateOfBirth is a valid date or date
string so if an invalid value gets passed in you are going to have errors. I
would check for a valid input and add error handling also.

The function would look more like the following.

Function Age(varDateofBirth As Variant) As Integer
Dim varAge As Variant
Dim dteDate as Date

On Error GoTo Proc_Error

If IsDate(varDateofBirth) = False Then
Age = 0: Exit Function
else dteDate = CDate(varDateofBirth)

varAge = DateDiff("yyyy", dteDate , Now)
If Date < DateSerial(Year(Now), Month(dteDate ), _
Day(dteDate )) Then
varAge = varAge - 1
End If
Age = CInt(varAge)
Exit Function

Proc_Error:
Age = 0 'Or a negative number if you want to see that an error
'has occurred

'Optionally generate a message, if you wish
'Msgbox Err.Number & ": " & Err.Description,,"Function Age"

End Function***


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Check that your Age function is working properly:

Debug.Print Age(#1/1/1999#)

I'm assuming that the field for the DOB is a Date Time data type. If not,
that could be a problem.
 
Pouvez vous parlez en fançais
I spike french


Le 19/03/10 19:58, dans (e-mail address removed), « John
 
THANK YOU. yes, this did it! I got the error message saying (Combile
Error:Expected variable or procedure, not module). The variable name was same
as a module, so I fixed it! TGIF, Thanks alot.
 
Cool. Are the two lines below on the top of each module in your database? I
believe that one of them can stop the problem from happening by catching such
things during a debug.

Option Compare Database
Option Explicit
 
You're right, Jerry. Somehow the "Option Explicit" got deleted, my mistake.
Thanks again.
 
Back
Top