Solved

MS ACCESS - Calc Age at Death but handle null dates

Posted on 2006-06-09
13
808 Views
Last Modified: 2007-12-19
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
0
Comment
Question by:COBOLforever
  • 3
  • 3
  • 3
  • +3
13 Comments
 
LVL 9

Accepted Solution

by:
raopsn earned 100 total points
ID: 16870165
Try

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

Expert Comment

by:Patrick Matthews
ID: 16870181
Hi COBOLforever,

Use IsNull().

Regards,

Patrick
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 16870188
Satya,

Sorry, did not refresh in time :)

Regards,

Patrick
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 34

Expert Comment

by:jefftwilley
ID: 16870194
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
 
LVL 34

Assisted Solution

by:jefftwilley
jefftwilley earned 100 total points
ID: 16870249
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
 
LVL 58

Expert Comment

by:harfang
ID: 16870287
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
 
LVL 58

Expert Comment

by:harfang
ID: 16870301
PS: do not change anything else, e.g. leave both parameters untyped.
(°v°)
0
 
LVL 44

Expert Comment

by:GRayL
ID: 16871590
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
 
LVL 44

Expert Comment

by:GRayL
ID: 16871607
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 Comment

by:COBOLforever
ID: 16871893
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
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16872187
Thanks COBOL, Happy to help.
J
0
 
LVL 58

Expert Comment

by:harfang
ID: 16872899
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
 
LVL 44

Expert Comment

by:GRayL
ID: 16872950
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

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question