Calculated field won't display properly

[Sue Compelling]

Hi

I have derived a calculated field of Age (years and months) by using an
individual's DOB though the final result is displaying: (eg)

13 (years) 4.52 (months) when I want it to display
13 (years) 5 (months)

Help ...

My qry is

SELECT IIf(IsNull([SecondName]),[FirstName] & " " & [LastName],[FirstName] &
" " & [SecondName] & " " & [LastName]) AS NameCombined, TblStudents.DOB,
(Date()-[dob])/365 AS AgeComb, Left([agecomb],2) AS AgeYr, Mid([agecomb],4,2)
AS AgeMthRaw, (12*[agemthraw]/100) AS AgeMth, ([AgeYr] & " (years) " &
Format([AgeMth],"@") & " (months)") AS Age
FROM TblStudents;


TIA

 

[Brian]

You need to either round the age or use the next full month.

Round(4.52,0) will round 4.52 to 0 decimal places, giving you 5.
Round(4.32,0) will round 4.32 to 0 decimal places, giving you 4.

Int(4.52)+1 will give you the greatest number less than 4.52 (4), then add
one = 5
Int(4.32)+1 will give you the greatest number less than 4.32 (4), then add
one = 5

Note that the Round function uses accountant's rounding, not general
mathematical rounding. This affects only numbers rounded based on a 5 in the
last place; these are rounded up or down depending on whether the next digit
to the left is odd (round up) or even (round down). Round (4.5,0) will result
in 4, but Round (5.5,0) will result in 6.

 

[John Spencer]

You might try using the following expression to get the age in months (all on
one line)
DateDiff("m",StartDate,Date()) +
(Format(StartDate,"mmdd")> Format(Date(),"mmdd"))

To get the years integer divide by 12
(DateDiff("m",StartDate,Date()) +
(Format(StartDate,"mmdd")> Format(Date(),"mmdd")))\ 12

To get the months use the modulus operator
(DateDiff("m",StartDate,Date()) +
(Format(StartDate,"mmdd")> Format(Date(),"mmdd"))) Mod 12

OR use the VBA function found the "More Complete DateDiff Function" Graham
Seach and Doug Steele wrote.

http://www.accessmvp.com/djsteele/Diff2Dates.html

You specify how you want the difference between two date/times to be
calculated by providing which of ymdhns (for years, months, days, hours,
minutes and seconds) you want calculated.

For example:

?Diff2Dates("y", #06/01/1998#, #06/26/2002#)
4 years
?Diff2Dates("ymd", #06/01/1998#, #06/26/2002#)
4 years 25 days
?Diff2Dates("ymd", #06/01/1998#, #06/26/2002#, True)
4 years 0 months 25 days

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Sue Compelling]

Thanks Brian - so simple when you know how ... cheers
--
Sue Compelling

You need to either round the age or use the next full month.

Round(4.52,0) will round 4.52 to 0 decimal places, giving you 5.
Round(4.32,0) will round 4.32 to 0 decimal places, giving you 4.

Int(4.52)+1 will give you the greatest number less than 4.52 (4), then add
one = 5
Int(4.32)+1 will give you the greatest number less than 4.32 (4), then add
one = 5

Note that the Round function uses accountant's rounding, not general
mathematical rounding. This affects only numbers rounded based on a 5 in the
last place; these are rounded up or down depending on whether the next digit
to the left is odd (round up) or even (round down). Round (4.5,0) will result
in 4, but Round (5.5,0) will result in 6.

Hi

I have derived a calculated field of Age (years and months) by using an
individual's DOB though the final result is displaying: (eg)

13 (years) 4.52 (months) when I want it to display
13 (years) 5 (months)

Help ...

My qry is

SELECT IIf(IsNull([SecondName]),[FirstName] & " " & [LastName],[FirstName] &
" " & [SecondName] & " " & [LastName]) AS NameCombined, TblStudents.DOB,
(Date()-[dob])/365 AS AgeComb, Left([agecomb],2) AS AgeYr, Mid([agecomb],4,2)
AS AgeMthRaw, (12*[agemthraw]/100) AS AgeMth, ([AgeYr] & " (years) " &
Format([AgeMth],"@") & " (months)") AS Age
FROM TblStudents;


TIA

 

[Sue Compelling]

Thanks John - always appreciate the learning from your responses ... cheers
--
Sue Compelling

You might try using the following expression to get the age in months (all on
one line)
DateDiff("m",StartDate,Date()) +
(Format(StartDate,"mmdd")> Format(Date(),"mmdd"))

To get the years integer divide by 12
(DateDiff("m",StartDate,Date()) +
(Format(StartDate,"mmdd")> Format(Date(),"mmdd")))\ 12

To get the months use the modulus operator
(DateDiff("m",StartDate,Date()) +
(Format(StartDate,"mmdd")> Format(Date(),"mmdd"))) Mod 12

OR use the VBA function found the "More Complete DateDiff Function" Graham
Seach and Doug Steele wrote.

http://www.accessmvp.com/djsteele/Diff2Dates.html

You specify how you want the difference between two date/times to be
calculated by providing which of ymdhns (for years, months, days, hours,
minutes and seconds) you want calculated.

For example:

?Diff2Dates("y", #06/01/1998#, #06/26/2002#)
4 years
?Diff2Dates("ymd", #06/01/1998#, #06/26/2002#)
4 years 25 days
?Diff2Dates("ymd", #06/01/1998#, #06/26/2002#, True)
4 years 0 months 25 days

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hi

I have derived a calculated field of Age (years and months) by using an
individual's DOB though the final result is displaying: (eg)

13 (years) 4.52 (months) when I want it to display
13 (years) 5 (months)

Help ...

My qry is

SELECT IIf(IsNull([SecondName]),[FirstName] & " " & [LastName],[FirstName] &
" " & [SecondName] & " " & [LastName]) AS NameCombined, TblStudents.DOB,
(Date()-[dob])/365 AS AgeComb, Left([agecomb],2) AS AgeYr, Mid([agecomb],4,2)
AS AgeMthRaw, (12*[agemthraw]/100) AS AgeMth, ([AgeYr] & " (years) " &
Format([AgeMth],"@") & " (months)") AS Age
FROM TblStudents;


TIA