By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,921 Members | 1,466 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,921 IT Pros & Developers. It's quick & easy.

Calculating Age - "Invalid use of Null" Error

P: n/a
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
Share this Question
Share on Google+
5 Replies


P: n/a
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

P: n/a
Thank you Allen - that works fine.

Apr 20 '06 #3

P: n/a
"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

P: n/a
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

P: n/a
"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.