Change date calculation result to specific format 1

[Dophia]

Hello Everyone: I am having trouble converting an animal's age, such as the result of a calculation as 1.3 , to 1 year 4 months.

Can anyone tell me how to do this?

This is the function that was given to me by DHookum, a long time ago:

Public Function GetEstAge(strInterval As Variant, _
intAgeEst As Variant, ArrDate As Variant _
) As Variant
If Not (IsNull(strInterval) Or IsNull(intAgeEst)) Then
Select Case strInterval
Case "week(s)"
strInterval = "ww"
Case "month(s)"
strInterval = "m"
Case "year(s)"
strInterval = "yyyy"
End Select

'
GetEstAge = Format(((Date - DateAdd(strInterval, -intAgeEst, ArrDate)) / 365.25), "0.0")

Else

GetEstAge = Null
End If
End Function

Thank you, Sophia

 

[dhookom]

Must have been a really long time ago. I would use Doug Steele's function.

Duane
Hook'D on Access
MS Access MVP

 

[Dophia]

Hi Duane: The code you gave me is part of a bigger problem and I may not have posted all the necessary details. It was a post from 2006 named "How to convert a combo box with text to a date?"

What you gave me worked, but the end resulting format was not as I wanted it...such as 1 year 3 months, instead of 1.2.

Sophia

 

[dhookom]

You can use the function from Doug with the 1.2 to get 1 year 3 months.

Duane
Hook'D on Access
MS Access MVP

 

[Dophia]

Hi again: Can you tell me how to use it? I can't see any instructions. I assume I would set up Date1 and Date2 in a table, but then what should the query hold?

Thanks for your help,
Sophia

 

[dhookom]

How do you want to use this? Do you have a animal date of birth in a field? I assume you want to see the age as of the current date.

Duane
Hook'D on Access
MS Access MVP

 

[Dophia]

Hi again:

I have the animal's date of birth (Date_of_Birth) in a field and I would like to know two things. The current age based on today's date and the age they were when left the animal shelter, for information purposes. I have the date that they left, as CommDate.

Sophia

 

[dhookom]

Add Doug's function in a standard module. Then anywhere you need to display the age in years and months, use:

Diff2Dates("ym", [Date_of_Birth], Date())

If this is a control source of a text box, remember to add an equal sign.



Duane
Hook'D on Access
MS Access MVP

 

[Dophia]

Thank you Duane.

Sophia