# MS ACCESS - Calc Age at Death but handle null dates

Hi Experts,
I have a function that works as long as the dates are not null but not otherwise - can you help?
If one of the dates is null I get "Invalid use of Null"

I tried putting: IF LesserDate Is not null
IF GreaterDate Is not null
(and matching End If's at the end)
but it also errors out (with Runtime '424' Object Required)

Here's the function- thanks:

Function CalcDate(LesserDate, GreaterDate) As Integer
' Returns the CalcDate in years between 2 dates
' Doesn't handle negative date ranges i.e. LesserDate > GreaterDate

If Month(GreaterDate) < Month(LesserDate) Or (Month(GreaterDate) = _
Month(LesserDate) And Day(GreaterDate) < Day(LesserDate)) Then
CalcDate = Year(GreaterDate) - Year(LesserDate) - 1
Else
CalcDate = Year(GreaterDate) - Year(LesserDate)
End If
End Function
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Try

IF NOT (ISNULL(LesserDate)  AND ISNULL(GreaterDate) )
End IF
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
Hi COBOLforever,

Use IsNull().

Regards,

Patrick
0
Commented:
Satya,

Sorry, did not refresh in time :)

Regards,

Patrick
0
Commented:
You also need to define your variables

Function CalcDate(LesserDate as date, GreaterDate as date) As Integer
' Returns the CalcDate in years between 2 dates
' Doesn't handle negative date ranges i.e. LesserDate > GreaterDate

If Month(GreaterDate) < Month(LesserDate) Or (Month(GreaterDate) = _
Month(LesserDate) And Day(GreaterDate) < Day(LesserDate)) Then
CalcDate = Year(GreaterDate) - Year(LesserDate) - 1
Else
CalcDate = Year(GreaterDate) - Year(LesserDate)
End If
End Function
J
0
Commented:
You might also want to make sure your end date is always greater than your start date.

Function CalcDate(LesserDate as date, GreaterDate as date) As Integer
' Returns the CalcDate in years between 2 dates
' Doesn't handle negative date ranges i.e. LesserDate > GreaterDate
if (isnull(LesserDate) or isnull(GreaterDate)) or (LesserDate > GreaterDate) then
msgbox"Input Error"
else
If Month(GreaterDate) < Month(LesserDate) Or (Month(GreaterDate) = _
Month(LesserDate) And Day(GreaterDate) < Day(LesserDate)) Then
CalcDate = Year(GreaterDate) - Year(LesserDate) - 1
Else
CalcDate = Year(GreaterDate) - Year(LesserDate)
End If
End If
End Function
J
0
Commented:
Hello COBOLforever

Your function is fine. You only need to allow it to *return* Null, hence:

Function CalcDate(LesserDate, GreaterDate) As Integer  ' <-- remove "As Integer"

It will then work as expected.

Cheers!
(°v°)
0
Commented:
PS: do not change anything else, e.g. leave both parameters untyped.
(°v°)
0
Commented:
Why not use:

Int(DateDiff("m",LesserDate,GreaterDate)/12)

and throw the UDF away.

For example, if the table contains a person's name as pname, birthdate and deathdate, run this query:

Select PName,Birthdate, DeathDate, Int(DateDiff("m",Birthdate, DeathDate)/12) as AgeAtDeath from myTable;
0
Commented:
I guess you could make it simpler by using just years:

Select PName,Birthdate, DeathDate, DateDiff("yyyy",Birthdate, DeathDate) as AgeAtDeath from myTable;
0
Author Commented:
Raopsn was the first to provide the direct answer, but Jeff offered additional logic - I increased points and split
(harfang - yours was good but I needed numerics back)
thanks xperts
0
Commented:
Thanks COBOL, Happy to help.
J
0
Commented:
Hello COBOLforever

As long as you leave the "...) As Integer", your function can do only that: return an integer, even if there are Null dates in the parameters. You should leave the function untyped, so that you would get an Integer when the age can be calculated or Null otherwise. So "I needed numerics back" is not an argument. You would get "numerics" back.

What do you have now? Return 0 when one of the dates is Null?

Anyway, glad you found something that works for you.
(°v°)
0
Commented:
And I would like to know why you feel you had to use a UDF when a built-in function does the job directly?
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.