471,579 Members | 1,533 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,579 software developers and data experts.

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

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

Apr 20 '06 #3
"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

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
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.
Apr 20 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by XIAOLAOHU | last post: by
reply views Thread by lumer26 | last post: by
1 post views Thread by lumer26 | last post: by
reply views Thread by lumer26 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.