Solved

MS ACCESS - Calc Age at Death but handle null dates

Posted on 2006-06-09
13
796 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
 
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

910 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now