Age Query

[UberMonkey83]

Hi, i am not so good at this yet, could you help me design a query that would use the current date and the birth date of an individual to determine age? i need to run queries for individuals who are 21+. Thanks for your time.

 

[lameid]

Under your DOB field put criteria that looks like this:

<=DateAdd("yyyy",-21 , Date())

That basically gives you anyone that was born on or before 21 years prior to todays date. Same thing you asked for, but a much faster way for the query to run.

 

[PHV]

In the query grid:[tt]
Field Age: DateDiff('yyyy',[birth date],Date())+(Format([birth date],'mmdd')>Format(Date(),'mmdd'))

Criteria >=21[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lameid]

Whenever you can avoid using criteria on a calculated expression, do so. That kind of query is intrinsically slow...

Even faster than my initial suggestion would be setting a control on a form equal to DateAdd("yyyy",-21 , Date()) and using that control as criteria instead.

 

[MichaelRed]

follow PHV's advice the other generates erronous return values approx 50% of hte time, as it doen not distinguish WHEN in the year the DOB is.

Datediff return hte number of INTERVAL units.

e.g.:

? dateDiff("yyyy", #12/31/05#, #1/1/06#)
1
? datediff("yyyy", #1/1/05#, #1/1/06#)
1



MichaelRed

 

[lameid]

PHV's method takes into account the issue you point out of Datediff by using a more complex expression.

My method uses Dateadd...

Given that today is 10/6/2006...

? DateAdd("yyyy",-21 , Date())
10/6/1985

So a query with a where clause of


Where DOB <= DateAdd("yyyy",-21 , Date())

Is going to give anyone that was born 21 years ago or earlier, making them at least 21.

It works. It should be must faster than PHV's. However, if you want to know someone's age in years, definitely use PHV's expression.