Calculating Age - "Invalid use of Null" Error

[Stewart]

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.

[Allen Browne]

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
news:11**********************@t31g2000cwb.googlegr oups.com...

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.

[Stewart]

Thank you Allen - that works fine.

[Keith Wilby]

"Stewart" <St******@vodafone.net> wrote in message
news:11**********************@t31g2000cwb.googlegr oups.com...

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

[Stewart]

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
_________________________________________

[Keith Wilby]

"Stewart" <St******@vodafone.net> wrote in message
news:11*********************@i40g2000cwc.googlegro ups.com...

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.