Distance between dates.

[chiara]

My apologies if this has been posted somewhere in this plethora of information, but in my search I couldn't find it!

I've built a dbase (with Access 2000) for a cemetary in order to log information about the persons buried there. The folks at the cemetary are currently inputing old info (from archive log books) into the dbase, including date of birth and date of death. They've asked me to come up with a I function to determine the "age" the person was when they were buried. To be more precise, I have date of birth and date of death and I need to find the number of years, months, and days between those two dates.

Such as, so and so lived for 80 years, 3 months and 28 days. (Taking into account leap years and the month in which the person died.)

I'd appreciate any help in this matter. I've been wracking my brain trying to come up with something to figure this out and it's just not coming together.

Thanks ahead of time!

 

[Mike375]

That is quite tricky. Hopefully someone will have the answer. I can do age and days, although I am sure my way is the very long way around

Age: (DateDiff("yyyy",[CL DOB],Now())+Int(Format(Now(),"mmdd")<Format([CL DOB],"mmdd")))

That will give you age in years and you could replace Now() with your date of death field.

Now to days. Say date of birth is 28/1/1936 and date of death was 15/5/2004. You make new fields that use Mid so that you can get 28/1/2004 from 28/1/1936. That then allows the number of days to be done.

The problem with months is that for me datediff will give 2 months between 31/3/04 and the today but 1 month for between 1/4/04 and today.

Have fun

Mike

 

[Jon K]

Bob posted a CalcAge function in this thread:-

http://www.access-programmers.co.uk/forums/showthread.php?t=33748&highlight=CalcAge

 

[Mike375]

Jon,

Do you know which one of those works for age in years, months and days.

I just went to back to what I use to do a couple of years ago and this is for Age Next Birthday

[CL DOB] Is where date of birth is.

First field in query

AgeNB: ((Date()-[CL DOB])/365.25)+1

Then

Anb: Int([AgeNB])

Results look like this.

Anb.........AgeNB.............................CL DOB
57...........57.1341546885695............30/03/1948

Perhaps what is needed is Month([CL DOB]) to give the numbers 1 to 12 and then have something based on the number of days in each month.

Mike

 

[Jon K]

Jon,

Do you know which one of those works for age in years, months and days.

The CalcAge function marked in red in the thread (i.e. in raskew Bob's third post). The Age returned by the function is in the format 80.3.28. You can replace the periods with years, months and days.


If you need to make both start date and end date inclusive, you can use +1 like this:-

Dim vYears As Integer, vMonths As Integer, vDays As Integer
vdate2 = vdate2 + 1
vMonths = DateDiff("m", vDate1, vdate2)

Jon

 

[raskew]

The search goes on. You might give this a try:

Function Agecount6(ByVal pdob As Date, _
                     Optional ByVal pEdte As Variant, _
                     Optional ByVal pWhat As Variant) As String

'*****************************************************
'Purpose:   Display age or difference between
'           two dates with options to display
'           in any variation of years, months,
'           days.
'Coded by:  raskew
'Inputs:    1) ? Agecount6(#3-Mar-80#) 'defaults
'                to current date & "ymd" display
'
'           2) ? Agecount6(#3-Mar-80#, "4/25/04")
'                Uses PEdte in place of date(),
'                and default "ymd" display

'           3) ? Agecount6(#3-Mar-80#, "4/25/04", "d")
'                 Same as 2), but with display as days
'
'Output:    1)  24 years, 1 month, 15 days
'           2)  24 years, 1 month, 22 days
'           3)  8819 days
'*****************************************************
                   
Dim dte2      As Date
Dim dteMyDate As Date
Dim intHold   As Integer
Dim n         As Integer
Dim strHold   As String
Dim strHold2  As String
Dim strTemp   As String
Dim strWhat   As String

    strWhat = IIf(IsMissing(pWhat), "ymd", pWhat)
    
    dteMyDate = pdob
    dte2 = IIf(IsMissing(pEdte), Date, pEdte)
    For n = 1 To Len(strWhat)
       strHold = Mid(strWhat, n, 1)
       Select Case strHold

          Case "y"
             intHold = DateDiff("yyyy", dteMyDate, dte2) + _
                      (dte2 < DateSerial(Year(dte2), Month(dteMyDate), Day(dteMyDate)))
             dteMyDate = DateAdd("yyyy", intHold, dteMyDate)
             strHold2 = strHold2 & LTrim(Str(intHold)) & " year" & IIf(intHold <> 1, "s, ", ", ")

          Case "m"
             intHold = DateDiff("m", dteMyDate, dte2) + (Day(dteMyDate) > Day(dte2))
             dteMyDate = DateAdd("m", intHold, dteMyDate)
             strHold2 = strHold2 & LTrim(Str(intHold)) & " " & "month" & IIf(intHold <> 1, "s, ", ", ")

          Case "d"
             intHold = DateDiff("d", dteMyDate, dte2)
             strHold2 = strHold2 & LTrim(Str(intHold)) & " " & "day" & IIf(intHold <> 1, "s", "")

       End Select
    Next n
    
    Agecount6 = strHold2

End Function

HTH - Bob

 

[Mike375]

Bob,

That is unreal.

Where and how would I put something like that.

The only coding I ever do is to open Word and insert data into Bookmarks and I have that on labels

Private Sub Label18_Click()


End Sub

Could some of it be removed so that it would work as a field in a query?

I think when I do NewField: I am limited to 255 charcters of scace.

Thanks.

Mike

 

[chiara]

First off, thanks for all the input, looks like you guys are having fun.

I'm with the guy above me. As nice as the CalcAge function is, I kinda don't know what to do with it. I've tried using it as an event procedure on my data entry form, but that didn't seem to work. I'm thinking maybe I'm not referencing the control correctly or something.

Any ideas? Can someone maybe be a bit more specific in where I should be putting this and what is referencing what?

I get that vdate1 is Birthdate and vdate 2 is Deathdate, but how do I tell Access where to find those dates?

 

[raskew]

Hi -

1) Copy/paste the code into a new module and save it.

2) Create your query and pull in name, DOB, DOD.

3) Add a calculated field, e.g.

AgeAtDeath: Agecount6([DOB], [DOD])
(assuming that you want years, months and days returned)

4) Run the query.

Here's an example you can play with. I used Northwind's Employees table to
return the age at date of hire of each of the employees. Just copy/paste
the code into a new query in Northwind, then let it rip.

SELECT
    Employees.LastName
  , Employees.FirstName
  , Employees.BirthDate
  , Employees.HireDate
  , Agecount6([BirthDate],[HireDate]) AS HireAge
FROM
   Employees;

Hope that'll be enough to get you started.

Bob

 

[raskew]

Here's a slightly more elaborate example--written in A97--

that expands on the logic used in the previous post.

Bob

 

[Mike375]

Bob,

That is just about the neatest thing I have seen and your instructions worked to perfection.

I made a table with fields and DOD and DOB and another table with Born and Died for the fields and both worked. But how does Agecount6 "connect" itself to DOD and DOB?

Mike

 

[Mike375]

Bob

My date of birth is 30/3/1948. It gives 56 years, 1 month, 19 days as my age.

If I change it to 31/3/1948 it also gives 56 years, 1 month, 19 days as my age.

29/3/48 gives 56 years, 1 month, 20 days

1/4/1948 gives 56 years, 1 month, 18 days

I wish I has not been on this thread

Mike

 

[Mike375]

Bob,

I just tried the following to see if I could do it in the normal query setup.

Age: (DateDiff("yyyy",[CL DOB],Now())+Int(Format(Now(),"mmdd")<Format([CL DOB],"mmdd"))) that gave me age in years and 56 for me with DoB of 30/3/48.

Then another field with Date()-30/3/2004. If my dob was after the current mth/day then 2003. That gave days.

I then tried both 30.416 and 30.4375 for average days in a month (using 365 and 365.25 days) so answer in Months was 1.64387164650184

Another field to 30.416*.64387164650184 for days. If the answer is rounded it looks like the results about where the code is. Obviously some Mid and IIf type stuff (at least for me) would need to be done to pull out the stuff like
.64387164650184 and decide wheter to use 2004 or 2003

The fact that the code is giving the same number of days in age for 30/3/48 and 31/3/48 suggests there is some rounding. What say ye?

Mike

 

[raskew]

Mike -

Good point--one I'd overlooked entirely.

The problem stems from using the dateadd function to
calculate from a 31-day month to a 30-day month.
You can see it in this example (I'm using medium date to
avoid confusion with regional date formats)

? format(dateadd("m", 1, #31-Mar-48#), "medium date")
30-Apr-48

? format(dateadd("m", 1, #30-Mar-48#), "medium date")
30-Apr-48

? format(dateadd("m", 1, #29-Mar-48#), "medium date")
29-Apr-48

If you calculate using strictly the "d" option, you'll see
that the problem doesn't exist.

Having said that, I'll have to get back to you on working
around the month issue.

Bob

 

[raskew]

Mike -

Try adding the following:

Dim dte2      As Date
Dim dteMyDate As Date
[COLOR=Blue]Dim diffHold  As Integer[/COLOR]
Dim intHold   As Integer
Dim n         As Integer
Dim strHold   As String
Dim strHold2  As String
Dim strTemp   As String
Dim strWhat   As String

Case "m"
             intHold = DateDiff("m", dteMyDate, dte2) + (Day(dteMyDate) > Day(dte2))
             [COLOR=Blue]diffHold = Day(dteMyDate) - Day(DateAdd("m", intHold, dteMyDate))[/COLOR]
             dteMyDate = DateAdd("m", intHold, dteMyDate) + diffHold
             strHold2 = strHold2 & LTrim(Str(intHold)) & " " & "month" & IIf(intHold <> 1, "s, ", ", ")

Test:

? agecount6(#31-Mar-48#, date())
56 years, 1 month, 18 days

? agecount6(#30-Mar-48#, date())
56 years, 1 month, 19 days

? agecount6(#29-Mar-48#, date())
56 years, 1 month, 20 days

 

[chiara]

I love you, I love you, I love you ALL!!!

It's working like a charm and I could just kiss every one of you. THANKS!!

The Demo ended up making the difference for me, that was great. THANKS AGAIN!

My heroes.