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
COBOLforeverAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

raopsnCommented:
Try

IF NOT (ISNULL(LesserDate)  AND ISNULL(GreaterDate) )
  ' your logic here
End IF
0

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
Patrick MatthewsCommented:
Hi COBOLforever,

Use IsNull().

Regards,

Patrick
0
Patrick MatthewsCommented:
Satya,

Sorry, did not refresh in time :)

Regards,

Patrick
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

jefftwilleyCommented:
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
jefftwilleyCommented:
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
harfangCommented:
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
harfangCommented:
PS: do not change anything else, e.g. leave both parameters untyped.
(°v°)
0
GRayLCommented:
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
GRayLCommented:
I guess you could make it simpler by using just years:

Select PName,Birthdate, DeathDate, DateDiff("yyyy",Birthdate, DeathDate) as AgeAtDeath from myTable;
0
COBOLforeverAuthor 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
jefftwilleyCommented:
Thanks COBOL, Happy to help.
J
0
harfangCommented:
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
GRayLCommented:
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.