?
Solved

MS ACCESS - Calc Age at Death but handle null dates

Posted on 2006-06-09
13
Medium Priority
?
818 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
  • +3
13 Comments
 
LVL 9

Accepted Solution

by:
raopsn earned 400 total points
ID: 16870165
Try

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

Expert Comment

by:Patrick Matthews
ID: 16870181
Hi COBOLforever,

Use IsNull().

Regards,

Patrick
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 16870188
Satya,

Sorry, did not refresh in time :)

Regards,

Patrick
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 400 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
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…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

762 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