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(DateOfBir th) Or (Month(DateToda y) =
Month(DateOfBir th) And Day(DateToday) < Day(DateOfBirth )) Then
Age = Year(DateToday) - Year(DateOfBirt h) - 1
Else
Age = Year(DateToday) - Year(DateOfBirt h)
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******@vodaf one.net> wrote in message
news:11******** **************@ t31g2000cwb.goo glegroups.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(DateOfBir th) Or (Month(DateToda y) =
Month(DateOfBir th) And Day(DateToday) < Day(DateOfBirth )) Then
Age = Year(DateToday) - Year(DateOfBirt h) - 1
Else
Age = Year(DateToday) - Year(DateOfBirt h)
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******@vodaf one.net> wrote in message
news:11******** **************@ t31g2000cwb.goo glegroups.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(DateOfBir th) Or (Month(DateToda y) =
Month(DateOfBir th) And Day(DateToday) < Day(DateOfBirth )) Then
Age = Year(DateToday) - Year(DateOfBirt h) - 1
Else
Age = Year(DateToday) - Year(DateOfBirt h)
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(DateOfBi rth) 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(DateOfBi rth) Then
If Month(DateToday ) < Month(DateOfBir th) Or (Month(DateToda y) =
Month(DateOfBir th) And Day(DateToday) < Day(DateOfBirth )) Then
Age = Year(DateToday) - Year(DateOfBirt h) - 1
Else
Age = Year(DateToday) - Year(DateOfBirt h)
End If
Else
Age = "Unknown"
End If

End Function
_______________ _______________ ___________

[Keith Wilby]

"Stewart" <St******@vodaf one.net> wrote in message
news:11******** *************@i 40g2000cwc.goog legroups.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.