Solved

MS ACCESS - Calc Age at Death but handle null dates

Posted on 2006-06-09
13
795 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

708 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

13 Experts available now in Live!

Get 1:1 Help Now