RMCDD997 said:
Thanks for you reply James. Could you read my replay to "fredg"? Thanks.
I created a table called tblAny and a form called MyForm with a textbox
called Today with a default value of =Date() for the purpose of running
a test:
tblAny
AID AutoNumber
DOB Date/Time
AID DOB
1 4/9/2006
2 2/2/2003
3 7/3/2004
4 7/18/2005
Forms!MyForm!Today.Value = #8/4/2007#
qryElapsedTimeSinceBirthday:
SELECT DOB, Int(Format(Forms!MyForm!Today.Value, 'yyyy.mmdd') -
Format([DOB], 'yyyy.mmdd')) & ' year(s)/' & (12 +
Int(Format(Forms!MyForm!Today.Value, 'mm.dd') - Format([DOB], 'mm.dd')))
Mod 12 & ' month(s)/' & Day(Forms!MyForm!Today.Value) - Day([DOB]) +
Abs(Day([DOB]) > Day(Forms!MyForm!Today.Value)) *
Day(DateSerial(Year([DOB]), Month([DOB]) + 1, 0)) & ' day(s)' As
ElapsedTime FROM tblAny;
!qryElapsedTimeSinceBirthday:
DOB ElapsedTime
4/9/2006 1 year(s)/3 month(s)/25 day(s)
2/2/2003 4 year(s)/6 month(s)/2 day(s)
7/3/2004 3 year(s)/1 month(s)/1 day(s)
7/18/2005 2 year(s)/0 month(s)/17 day(s)
Now that the test seems to work, change the expression to get the [DOB]
value from the form:
Age.ControlSource
= Int(Format(Today.Value, 'yyyy.mmdd') - Format(DOB.Value, 'yyyy.mmdd'))
& ' year(s)/' & (12 + Int(Format(Today.Value, 'mm.dd') -
Format(DOB.Value, 'mm.dd'))) Mod 12 & ' month(s)/' & Day(Today.Value) -
Day(DOB.Value) + Abs(Day(DOB.Value) > Day(Today.Value)) *
Day(DateSerial(Year(DOB.Value), Month(DOB.Value) + 1, 0)) & ' day(s)'
James A. Fortune
(e-mail address removed)