Date of Birth Expressions in a Query...

[SherryLynn]

How can I write an expression in a query that will give me a persons age based on their date of birth? So far, I have an expression which will give me the rounded number, however if the person is a child who is under one year in age - it rounds it up to 1. I need it to tell me if a person is less than 1 year in months, say 9months rather than rounding up to 1.

The expression I am presently using is:

Int(((Now()-[DOB])=0.75/365.25)

I hope someone can help. Thanks very much.

Sherry

 

[Guest_imported]

I think you just need to change the datatype from an int to a double. Change your line to this:

CDbl(((Now()-[DOB])=0.75/365.25)

This will return a double instead of a float. The reason you want a double is it allows decimal points in the result where as an integer always rounds

I hope this helps

 

[braindead2]

use a function
paste this code in a module
'(code from FAQ section modified to show months for less then 1 year old)

Function Age(Birthdate As String) As String
Age = DateDiff("yyyy", Birthdate, Date) + _
(Date < DateSerial(Year(Date), Month(Birthdate), Day(Birthdate)))
If Age <= 0 Then Age = DateDiff(&quot;m&quot;, Birthdate, Date) + _
(Date < DateSerial(Year(Date), Month(Birthdate), Day(Birthdate))) & &quot; months&quot;

End Function

now refer to it in a query as
month([DOB])

 

[braindead2]

error in first post. Will need to refer to function as
Age([DOB])
Sorry

 

[MichaelRed]

If it is &quot;O.K.&quot; to have some extraneous text, the following simple examples show a way to compute and format a &quot;age&quot; calculation. As with most date things, the &quot;trick&quot; is to calculate the date difference and then use some mod and div arith to get the piece parts.




MyDob =#8/21/42#
MyMnths = dateDiff(&quot;m&quot;, MyDob, Now)
? Str(MyMnths \ 12) & &quot; Years and&quot; & Str(MyMnths mod 12) & &quot; Months&quot;
58 Years and 10 Months

HisDob = #11/16/2000#
MyMnths = dateDiff(&quot;m&quot;, HisDob, Now)
? Str(MyMnths \ 12) & &quot; Years and&quot; & Str(MyMnths mod 12) & &quot; Months&quot;
0 Years and 7 Months


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[SherryLynn]

Thanks everyone! I got it to work thanks to all your help.

Sherry