472,950 Members | 2,056 Online

# Calculating Age - "Invalid use of Null" Error

Hi there,

I would like to calculate a person's age in years (between 2 dates). I
am using the following function for this calculation (source:
http://www.mvps.org/access/datetime/date0001.htm)
_________________________________________

Function Age(DateOfBirth, DateToday) As Integer
' Returns the Age in years between 2 dates

If Month(DateToday) < Month(DateOfBirth) Or (Month(DateToday) =
Month(DateOfBirth) And Day(DateToday) < Day(DateOfBirth)) Then
Age = Year(DateToday) - Year(DateOfBirth) - 1
Else
Age = Year(DateToday) - Year(DateOfBirth)
End If
End Function
_________________________________________

This seems to work fine. I've one small problem though: where the
DateOfBirth field is null, a run-time error occurs, i.e. "Invalid use
of Null".

Can anyone suggest a simple way of tweaking the above function in order
to handle null values?

Any help would be greatly appreciated!

Thanks,
Stewart.

Apr 20 '06 #1
5 3391
Try changing the first line from:
Function Age(DateOfBirth, DateToday) As Integer
to:
Function Age(DateOfBirth, DateToday) As Variant

If you still have a problem, see:
http://allenbrowne.com/func-08.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Stewart" <St******@vodafone.net> wrote in message
Hi there,

I would like to calculate a person's age in years (between 2 dates). I
am using the following function for this calculation (source:
http://www.mvps.org/access/datetime/date0001.htm)
_________________________________________

Function Age(DateOfBirth, DateToday) As Integer
' Returns the Age in years between 2 dates

If Month(DateToday) < Month(DateOfBirth) Or (Month(DateToday) =
Month(DateOfBirth) And Day(DateToday) < Day(DateOfBirth)) Then
Age = Year(DateToday) - Year(DateOfBirth) - 1
Else
Age = Year(DateToday) - Year(DateOfBirth)
End If
End Function
_________________________________________

This seems to work fine. I've one small problem though: where the
DateOfBirth field is null, a run-time error occurs, i.e. "Invalid use
of Null".

Can anyone suggest a simple way of tweaking the above function in order
to handle null values?

Any help would be greatly appreciated!

Thanks,
Stewart.

Apr 20 '06 #2
Thank you Allen - that works fine.

Apr 20 '06 #3
"Stewart" <St******@vodafone.net> wrote in message
Hi there,

I would like to calculate a person's age in years (between 2 dates). I
am using the following function for this calculation (source:
http://www.mvps.org/access/datetime/date0001.htm)
_________________________________________

Function Age(DateOfBirth, DateToday) As Integer
' Returns the Age in years between 2 dates

If Month(DateToday) < Month(DateOfBirth) Or (Month(DateToday) =
Month(DateOfBirth) And Day(DateToday) < Day(DateOfBirth)) Then
Age = Year(DateToday) - Year(DateOfBirth) - 1
Else
Age = Year(DateToday) - Year(DateOfBirth)
End If
End Function
_________________________________________

This seems to work fine. I've one small problem though: where the
DateOfBirth field is null, a run-time error occurs, i.e. "Invalid use
of Null".

Can anyone suggest a simple way of tweaking the above function in order
to handle null values?

You could wrap the whole thing in an IF ... THEN statement to make it either
fail silently or do something else:

If Not IsNull(DateOfBirth) Then
'Run the code
Else
'Do nothing or whatever
Age = 0
End If

HTH - Keith.
www.keithwilby.com

Apr 20 '06 #4
Thanks Keith,

I applied a wrapper as you suggested (see below) - this way I can
specify the null fields as "Unknown".

Cheers,
Stewart.
_________________________________________

Function Age(DateOfBirth, DateToday) As Variant
' Returns the Age in years between 2 dates

If Not IsNull(DateOfBirth) Then
If Month(DateToday) < Month(DateOfBirth) Or (Month(DateToday) =
Month(DateOfBirth) And Day(DateToday) < Day(DateOfBirth)) Then
Age = Year(DateToday) - Year(DateOfBirth) - 1
Else
Age = Year(DateToday) - Year(DateOfBirth)
End If
Else
Age = "Unknown"
End If

End Function
_________________________________________

Apr 20 '06 #5
"Stewart" <St******@vodafone.net> wrote in message
Thanks Keith,

I applied a wrapper as you suggested (see below) - this way I can
specify the null fields as "Unknown".

I see you took Allen's advice to change the function type to Variant so, yes
.... result!

Regards,
Keith.
Apr 20 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.