Date difference in years, months, days (1 Viewer)

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:58
Joined
Feb 28, 2001
Messages
27,001
Ah, if it says Date2 is null then I can point to the error for you.

Code:
If Date2 = 0 Then Date2 = Date

This will fail because NULL is not equal to 0. Or anything else, for that matter. In fact, NULL is not even equal to NULL. In essence, Access automatically says "NOT EQUAL" to ANY comparison involving a null.

You can fix this line with:

Code:
If Nz(Date2,0) = 0 Then Date2 = Date
 

dgreen

Member
Local time
Today, 04:58
Joined
Sep 30, 2018
Messages
397
Here's the updated code to support the Age Function. Thanks for the help.

Code:
Option Compare Database
Option Explicit
Public Function Age(Date1 As Variant, Optional Date2 As Variant = 0) As String
'https://www.access-programmers.co.uk/forums/threads/date-difference-in-years-months-days.121140/#post-1674999
'Provided by @arnelgp
'Fixes by @TheDBGuy
'Date1 = 8/1/97 and Date2 = 8/1/97 then Age(Date1, Date2) results in "1 year"
'Date1 = no date and Date2 = 1/1/92 then Age(Date1, Date2) results in "Unknown"
'Date1 = 9/1/18 and Date2 = no date then Age(Date1, Date2) results in "1 year 6 months and 7 days"
'Date1 = no date and Date2 = no date then Age(Date1, Date2) results in "Unknown"
Dim Year1 As Integer
Dim Month_1 As Integer
Dim Day1 As Integer
Dim temp As Date
Dim sAge As String
'the result will be Unknown if Date1 is empty or is not in a date format
Age = "Unknown"
If Trim(Date1 & "") = "" Then Exit Function
If IsDate(Date1) = False Then Exit Function
'the result will be empty if .....
Age = ""
If Nz(Date2, 0) = 0 Then Date2 = Date
temp = DateSerial(Year(Date2), Month(Date1), Day(Date1))
Year1 = Year(Date2) - Year(Date1) + (temp > Date2)
Month_1 = Month(Date2) - Month(Date1) - (12 * (temp > Date2))
'Day in date2 minus date1, gives the # of days
Day1 = Day(Date2) - Day(Date1)
'if day1 is less than 0 then
If Day1 < 0 Then
Month_1 = Month_1 - 1
Day1 = Day(DateSerial(Year(Date2), Month(Date2) + 1, 0)) + Day1 + 1
End If
'if Year1 is greater than 0 then
If Year1 > 0 Then
Age = Year1
'if the year # is greater than 1 use plural
If Year1 > 1 Then
        Age = Age & " years "
    'Otherwise use singluar
Else
Age = Age & " year "
    End If
    'if month_1 is greater than 0 and Day1 is equal to 0 then use below which will just join the year and month
If Month_1 > 0 And Day1 = 0 Then
Age = Age & " and "
End If
End If
'if month_1 is greater than 0 and based on the above the days are more than 0 then
If Month_1 > 0 Then
Age = Age & Month_1
If Month_1 > 1 Then
Age = Age & " months "
Else
Age = Age & " month "
End If
End If
If Day1 > 0 Then
Age = Age & " and " & Day1
If Day1 > 1 Then
Age = Age & " days"
Else
Age = Age & " day"
End If
End If
sAge = Trim(Replace(Age, "  ", " "))
If InStr(sAge, "and") = 1 Then _
sAge = Trim(Replace(sAge, "and", ""))
Age = sAge
End Function
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:58
Joined
Feb 28, 2001
Messages
27,001
OK, so ... does it work better now?
 

dgreen

Member
Local time
Today, 04:58
Joined
Sep 30, 2018
Messages
397
Better, yes.
Last fix is getting commas and ands correct. No commas if only two items and the and only between the last and 2nd to last group.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:58
Joined
Feb 28, 2001
Messages
27,001
That is probably an exercise best left to you since it is a matter of tweaking it to match your preferred appearance. Do you need to know anything else about how to do that?
 

dgreen

Member
Local time
Today, 04:58
Joined
Sep 30, 2018
Messages
397
Yes, I could use the assistance. I thought it would be as easy as adding some commas after the year and month values but that doesn't work.

What I'd like to see is are the following kinds of behaviors in terms of commas and ands.
1 year
1 year and 2 months
1 year, 2 months and 3 days
2 months and 1 day
10 days

Code:
Option Compare Database
Option Explicit
Public Function Age(Date1 As Variant, Optional Date2 As Variant = 0) As String
'https://www.access-programmers.co.uk/forums/threads/date-difference-in-years-months-days.121140/#post-1674999
'Provided by @arnelgp
'Fixes by @TheDBGuy
'Date1 = 8/1/97 and Date2 = 8/1/97 then Age(Date1, Date2) results in "1 year"
'Date1 = no date and Date2 = 1/1/92 then Age(Date1, Date2) results in "Unknown"
'Date1 = 9/1/18 and Date2 = no date then Age(Date1, Date2) results in "1 year 6 months and 7 days"
'Date1 = no date and Date2 = no date then Age(Date1, Date2) results in "Unknown"
Dim Year1 As Integer
Dim Month_1 As Integer
Dim Day1 As Integer
Dim temp As Date
Dim sAge As String
'the result will be Unknown if Date1 is empty or is not in a date format
Age = "Unknown"
If Trim(Date1 & "") = "" Then Exit Function
If IsDate(Date1) = False Then Exit Function
'the result will be empty if .....
Age = ""
'if Date2 is null/blank then make the value 0, then if 0 = 0 then make date2 = to today's date.
If Nz(Date2, 0) = 0 Then Date2 = Date
temp = DateSerial(Year(Date2), Month(Date1), Day(Date1))
Year1 = Year(Date2) - Year(Date1) + (temp > Date2)
Month_1 = Month(Date2) - Month(Date1) - (12 * (temp > Date2))
'Day in date2 minus date1, gives the # of days
Day1 = Day(Date2) - Day(Date1)
'if day1 is less than 0 then
If Day1 < 0 Then
Month_1 = Month_1 - 1
Day1 = Day(DateSerial(Year(Date2), Month(Date2) + 1, 0)) + Day1 + 1
End If
'if Year1 is greater than 0 then
If Year1 > 0 Then
Age = Year1
'if the year # is greater than 1 use plural
If Year1 > 1 Then
        Age = Age & " years "
    'Otherwise use singluar
Else
Age = Age & " year "
    End If
    'if month_1 is greater than 0 and Day1 is equal to 0 then use below which will just join the year and month
If Month_1 > 0 And Day1 = 0 Then
Age = Age & " and "
End If
End If
'if month_1 is greater than 0 and based on the above the days are more than 0 then
If Month_1 > 0 Then
Age = Age & Month_1
'if the month # is greater than 1 use plural
If Month_1 > 1 Then
Age = Age & " months "
'Otherwise use singluar
Else
Age = Age & " month "
End If
End If
If Day1 > 0 Then
Age = Age & " and " & Day1
'if the day is greater than 1 use the plural
If Day1 > 1 Then
Age = Age & " days"
'Otherwise use the singluar
Else
Age = Age & " day"
End If
End If
sAge = Trim(Replace(Age, "  ", " "))
If InStr(sAge, "and") = 1 Then _
sAge = Trim(Replace(sAge, "and", ""))
Age = sAge
End Function
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:58
Joined
Feb 28, 2001
Messages
27,001
Well, one approach is to erase your year, month, and day slots, then pre-compute the substrings like "1 year" and "2 months" and "3 days" before you actually build the final string. Then you can test for blank/not blank to decide whether you need a comma and/or an "and" in the mess. Just use the ampersand to concatenate the final strings.

Three non-blank strings? You need both "," and "and" so drop in Years & "," & Months & "and" & Days.

Two non-blank strings? You won't need a "," at all. Drop in the year string. If it is blank, then drop in Months "AND" Days. If years was not blank then drop in Years & "AND" & Months & Days (because with only two strings and Years already used, one of the remaining two will be blank.

One non-blank string? You need neither "," nor "and" so just drop in Years & Months & Days (because two of them will be empty.)

Hope that made sense.
 

Users who are viewing this thread

Top Bottom