Calculating Years Months Days DateDiff

M

m0aje

Registered User.
Local time
, 22:30
Joined
Mar 7, 2014
Messages
38
Hello,

I have created a cemetery database in Access 2003. I have three fields;
BirthDate, DeathDate, and Age.

Under properties in the AGE field in CONTROL I am using the following syntax:

=IIf([DeathDate],(DateDiff("m",[BirthDate],[DeathDate])\12 & " years and " & DateDiff("m",[BirthDate],[DeathDate]) Mod 12 & " month(s)"),"Living")

This works and gives me years and months results in my AGE field. I would like to add DAYS..ex.. 74 years, 5 months, and 7 days.

The "living" is when there is a birth date in the field but no death date. However, "living" shows up even if both fields are blank. Any help with that and adding "days" would be most appreciated.

Thanks,

m0aje
 
You haven't set a criteria for the conditonal part of the IIF()

You effectively have If DeathDate ? , calculation, else "living"

You should have IIf DeathDate is not null, calulation , "Living"
 
Use this function to return the time in the various formats.
Just end the time difference in seconds
vSecs = DateDiff("s",[BirthDate],[DeathDate])
USAGE: ElapsedTimeAsTextRecur(vSecs)

Code: 
Public Function ElapsedTimeAsTextRecur(ByVal pvSecs, Optional ByVal pvSecBlock)
'recursive time lapse given seconds
Dim vTxt
Dim iNum As Long
Const kDAY = 86400
Const kSECpYR = 31536000

'60 sec = 1 min             60 sec
'60 min = 1 hr            3600 sec
'24 hr = 1 day           86400 sec
'7 days = 1 week        604800 sec
'30 days = 1 month     2592000
'12 months = 1 year = 31536000

'YEARS
If IsMissing(pvSecBlock) Then pvSecBlock = kSECpYR
iNum = pvSecs \ pvSecBlock
    
    Select Case pvSecBlock
       Case kSECpYR   'yr
          sUnit = "years"
          If iNum > 0 Then
               vTxt = iNum & " years "
               pvSecs = pvSecs - (iNum * pvSecBlock)
          End If
          vTxt = vTxt & ElapsedTimeAsTextRecur(pvSecs, 2592000)
          
      Case 2592000    'MO
          sUnit = "months"
          If iNum > 0 Then
               If iNum > 11 Then iNum = 11
               vTxt = vTxt & iNum & " months "
               pvSecs = pvSecs - (iNum * pvSecBlock)
          End If
          vTxt = vTxt & ElapsedTimeAsTextRecur(pvSecs, 604800)
       
       Case 604800     'WEEK
          sUnit = "weeks"
          If iNum > 0 Then
               If iNum > 3 Then iNum = 3
               vTxt = vTxt & iNum & " weeks "
               pvSecs = pvSecs - (iNum * kDAY * 7)
          End If
          vTxt = vTxt & ElapsedTimeAsTextRecur(pvSecs, 86400)
       
       Case kDAY      'day
          sUnit = "days"
          If iNum > 0 Then
               vTxt = vTxt & iNum & " days "
               pvSecs = pvSecs - (iNum * kDAY)
          End If
          vTxt = vTxt & ElapsedTimeAsTextRecur(pvSecs, 3600)
       
       Case 3600       'hrs
          sUnit = "hrs"
          If iNum > 23 Then iNum = 23
          If iNum > 0 Then
               vTxt = vTxt & iNum & " hrs "
               pvSecs = pvSecs - (iNum * pvSecBlock)
          End If
          vTxt = vTxt & ElapsedTimeAsTextRecur(pvSecs, 60)
       
       Case 60         'min
          sUnit = "mins"
          If iNum > 0 Then
               vTxt = vTxt & iNum & " mins "
               pvSecs = pvSecs - (iNum * pvSecBlock)
          End If
          vTxt = vTxt & ElapsedTimeAsTextRecur(pvSecs, 1)
       
       Case Else
          
          sUnit = "secs"
          If pvSecs > 0 Then vTxt = vTxt & pvSecs & " seconds"
    End Select
    
ElapsedTimeAsTextRecur = vTxt
End Function
 
Minty said:
You haven't set a criteria for the conditonal part of the IIF()

You effectively have If DeathDate ? , calculation, else "living"

You should have IIf DeathDate is not null, calulation , "Living"
Click to expand...

Thanks for your reply Minty:

I tried the following in CONTROL in the AGE field.
=IIf([DeathDate] Is Null,"Living",(DateDiff("m",[BirthDate],[DeathDate])\12 & " year(s) and " & DateDiff("m",[BirthDate],[DeathDate]) Mod 12 & " month(s)"))
I got this to work, however I have some records that have no dates for birth or death.
I tried this:
=IIf([DeathDate] Is Null,"Living",IIf([BirthDate] And [DeathDate] Is Null,"No Dates",(DateDiff("m",[BirthDate],[DeathDate])\12 & " year(s) and " & DateDiff("m",[BirthDate],[DeathDate]) Mod 12 & " month(s)"))). There were no errors in the syntax, but leaving both dates out the results still listed "Living".
Still trying to get the "days" to display along with "years and months"

Again, thanks for your help.

r/moaje
 
Ranman256 said:
Use this function to return the time in the various formats.
Just end the time difference in seconds
vSecs = DateDiff("s",[BirthDate],[DeathDate])
USAGE: ElapsedTimeAsTextRecur(vSecs)

Code: 
Public Function ElapsedTimeAsTextRecur(ByVal pvSecs, Optional ByVal pvSecBlock)
'recursive time lapse given seconds
Dim vTxt
Dim iNum As Long
Const kDAY = 86400
Const kSECpYR = 31536000

'60 sec = 1 min             60 sec
'60 min = 1 hr            3600 sec
'24 hr = 1 day           86400 sec
'7 days = 1 week        604800 sec
'30 days = 1 month     2592000
'12 months = 1 year = 31536000

'YEARS
If IsMissing(pvSecBlock) Then pvSecBlock = kSECpYR
iNum = pvSecs \ pvSecBlock
    
    Select Case pvSecBlock
       Case kSECpYR   'yr
          sUnit = "years"
          If iNum > 0 Then
               vTxt = iNum & " years "
               pvSecs = pvSecs - (iNum * pvSecBlock)
          End If
          vTxt = vTxt & ElapsedTimeAsTextRecur(pvSecs, 2592000)
          
      Case 2592000    'MO
          sUnit = "months"
          If iNum > 0 Then
               If iNum > 11 Then iNum = 11
               vTxt = vTxt & iNum & " months "
               pvSecs = pvSecs - (iNum * pvSecBlock)
          End If
          vTxt = vTxt & ElapsedTimeAsTextRecur(pvSecs, 604800)
       
       Case 604800     'WEEK
          sUnit = "weeks"
          If iNum > 0 Then
               If iNum > 3 Then iNum = 3
               vTxt = vTxt & iNum & " weeks "
               pvSecs = pvSecs - (iNum * kDAY * 7)
          End If
          vTxt = vTxt & ElapsedTimeAsTextRecur(pvSecs, 86400)
       
       Case kDAY      'day
          sUnit = "days"
          If iNum > 0 Then
               vTxt = vTxt & iNum & " days "
               pvSecs = pvSecs - (iNum * kDAY)
          End If
          vTxt = vTxt & ElapsedTimeAsTextRecur(pvSecs, 3600)
       
       Case 3600       'hrs
          sUnit = "hrs"
          If iNum > 23 Then iNum = 23
          If iNum > 0 Then
               vTxt = vTxt & iNum & " hrs "
               pvSecs = pvSecs - (iNum * pvSecBlock)
          End If
          vTxt = vTxt & ElapsedTimeAsTextRecur(pvSecs, 60)
       
       Case 60         'min
          sUnit = "mins"
          If iNum > 0 Then
               vTxt = vTxt & iNum & " mins "
               pvSecs = pvSecs - (iNum * pvSecBlock)
          End If
          vTxt = vTxt & ElapsedTimeAsTextRecur(pvSecs, 1)
       
       Case Else
          
          sUnit = "secs"
          If pvSecs > 0 Then vTxt = vTxt & pvSecs & " seconds"
    End Select
    
ElapsedTimeAsTextRecur = vTxt
End Function
Click to expand...

Hello RanMan256,

Thank you for your reply. I appreciate the code you provided, but I don't fully understand where or how I would insert it and make it work with my application. I am using Access 2003 and I have very limited knowledge of VBA and SQL code. I am like a blind squirrel.... once in a while I find a nut but most of the time the folks on this forum are a big help to me.

Again thank you for taking the time to help me. I appreciate it.

r/ m0aje
 
m0aje said:
Thanks for your reply Minty:

I tried the following in CONTROL in the AGE field.
=IIf([DeathDate] Is Null,"Living",(DateDiff("m",[BirthDate],[DeathDate])\12 & " year(s) and " & DateDiff("m",[BirthDate],[DeathDate]) Mod 12 & " month(s)"))
I got this to work, however I have some records that have no dates for birth or death.
I tried this:
=IIf([DeathDate] Is Null,"Living",IIf([BirthDate] And [DeathDate] Is Null,"No Dates",(DateDiff("m",[BirthDate],[DeathDate])\12 & " year(s) and " & DateDiff("m",[BirthDate],[DeathDate]) Mod 12 & " month(s)"))). There were no errors in the syntax, but leaving both dates out the results still listed "Living".
Still trying to get the "days" to display along with "years and months"

Again, thanks for your help.

r/moaje
Click to expand...


Try this:

=DateDiff("m",[DOB],[CurrentDate])\12 & " year(s), " & DateDiff("m",[DOB],[CurrentDate]) Mod 12 & " month(s), " & DateDiff("d",[DOB],[CurrentDate]) Mod 12 & " day(s)"

Or

the following will show - if the person is still Living - "Age: " in front of years(s), Month(s) and day(s)

if the person is dead - "Died at: " in front of ....

IIf([DateDead] Is Null,"Age: " & DateDiff("m",[DateNaiss],Date())\12 & " year(s), " & DateDiff("m",[DateNaiss],Date()) Mod 12 & " month(s), " & DateDiff("d",[DateNaiss],Date()) Mod 12 & " day(s)","Died at: " & DateDiff("m",[DateNaiss],[DateDead])\12 & " year(s), " & DateDiff("m",[DateNaiss],[DateDead]) Mod 12 & " month(s), " & DateDiff("d",[DateNaiss],[DateDead]) Mod 12 & " day(s)")

of course replace [DateDead] with [DeathDate]
and
[DateNaiss] with [BirthDate]

Hope this help...

Serge L.
 
m0aje said:
Thanks for your reply Minty:

I tried the following in CONTROL in the AGE field.
=IIf([DeathDate] Is Null,"Living",(DateDiff("m",[BirthDate],[DeathDate])\12 & " year(s) and " & DateDiff("m",[BirthDate],[DeathDate]) Mod 12 & " month(s)"))
I got this to work, however I have some records that have no dates for birth or death.
I tried this:
=IIf([DeathDate] Is Null,"Living",IIf([BirthDate] And [DeathDate] Is Null,"No Dates",(DateDiff("m",[BirthDate],[DeathDate])\12 & " year(s) and " & DateDiff("m",[BirthDate],[DeathDate]) Mod 12 & " month(s)"))). There were no errors in the syntax, but leaving both dates out the results still listed "Living".
Still trying to get the "days" to display along with "years and months"

Again, thanks for your help.

r/moaje
Click to expand...


Try this:

=DateDiff("m",[DOB],[CurrentDate])\12 & " year(s), " & DateDiff("m",[DOB],[CurrentDate]) Mod 12 & " month(s), " & DateDiff("d",[DOB],[CurrentDate]) Mod 12 & " day(s)"

Or

the following will show - if the person is still Living - "Age: " in front of years(s), Month(s) and day(s)

if the person is dead - "Died at: " in front of ....

IIf([DateDead] Is Null,"Age: " & DateDiff("m",[DateNaiss],Date())\12 & " year(s), " & DateDiff("m",[DateNaiss],Date()) Mod 12 & " month(s), " & DateDiff("d",[DateNaiss],Date()) Mod 12 & " day(s)","Died at: " & DateDiff("m",[DateNaiss],[DateDead])\12 & " year(s), " & DateDiff("m",[DateNaiss],[DateDead]) Mod 12 & " month(s), " & DateDiff("d",[DateNaiss],[DateDead]) Mod 12 & " day(s)")

of course replace [DateDead] with [DeathDate]
and
[DateNaiss] with [BirthDate]

Hope this help...

Serge L.
 
Hello Serge,
Thank you for your reply. I apologize for the delay. For some reason there was no msg that you replied in my email. Anyway......
THANK YOU!!!!!!! THIS WORKS.
I have been messing with this for a few weeks on my own and could not get the syntax right. This will work great for my database. I am still a novice at this so I can use all the help I can get.
THANK YOU AGAIN! I am most grateful.

:D:D:D
 
Hi m0aje


I'm pleased that it solved your problem.... Like any Access users keep trying new things... Until next time...

Serge
 
You must log in or register to reply here.

Similar threads

Feral
Solved Finding Tenure Time
2
Replies
21
Views
312
Feral
Feral
L
Solved Problem calculating correct age from birth to death
Replies
11
Views
679
GPGeorge
GPGeorge
H
convert age to date of birth in three fields: year, month, and day.
Replies
3
Views
685
Pat Hartman
P
I
If a record is more than 5 years old write it down by 20% per year until £0, (but by months)
Replies
13
Views
572
CJ_London
C
D
How to select most current date by month, day and year
Replies
15
Views
462
Design by Sue
D

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom