Calculated field won't display properly

  • Thread starter Thread starter Sue Compelling
  • Start date Start date
S

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
 
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.
 
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
 
Thanks Brian - so simple when you know how ... cheers
--
Sue Compelling


Brian said:
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.

Sue Compelling said:
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
Click to expand...
Click to expand...
 
Thanks John - always appreciate the learning from your responses ... cheers
--
Sue Compelling


John Spencer said:
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 said:
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
Click to expand...
Click to expand...
 
Back
Top