Need help with this date of birth query

[Guest_imported]

How do I develop a query that will show me all members over the age 30 assuming that I have a D.O.B. field not a age field, so it doesn't need constant updating when folks get older.

 

[tlbroadbent]

Select * From MyTable
Where DOB <= dateadd(&quot;yyyy&quot;,-30,date()) Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[JoeMiller]

You can use this equation in a new column of your query as well, it will provide you the proper age of the person and then just set the criteria on the column to >30.

Age: IIf(IsNull([DOB]),0,DateDiff(&quot;yyyy&quot;,[DOB],Date())+(DateSerial(Year(Date()),Month([DOB]),Day([DOB]))>Date())) Joe Miller
joe.miller@flotech.net

 

[MichaelRed]

Both of the results ignore wheather DATE < DOB (Month and Year. Since DateDiff ('Year') only counts the Year transitions it doesn't quite tell the age (properly) depending on the DOB Month / Day and the Date Month/day. In a seperate response to the same inquiry in a different forum, a SMALL procedure group was provided which accounts for these vagaries.

MichaelRed
m.red@att.net

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

 

[tlbroadbent]

Michael,

I disagree with your characterization of dateadd(&quot;yyyy&quot;,-30,date()) as vague or not accounting properly for the age. Note the results from Access 2000. Looks very precise to me.

?dateadd(&quot;yyyy&quot;,-30,#9/10/2001#)
9/10/71

?dateadd(&quot;yyyy&quot;,-30,date())
9/11/71

DOB=#9/11/71#:?DOB <= dateadd(&quot;yyyy&quot;,-30,date())
True

DOB=#9/12/71#:?DOB <= dateadd(&quot;yyyy&quot;,-30,date())
False

And Joe's function works properly, also.

DOB=#9/11/71#:?IIf(IsNull(DOB),0,DateDiff(&quot;yyyy&quot;,DOB,Date())+(DateSerial(Year(Date()),Month(DOB),Day(DOB))>Date()))
30

DOB=#9/12/71#:?IIf(IsNull(DOB),0,DateDiff(&quot;yyyy&quot;,DOB,Date())+(DateSerial(Year(Date()),Month(DOB),Day(DOB))>Date()))
29

Can you provide evidence to the contrary? Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums. NOTE: Reference to the FAQ is not directed at any individual.

 

[MichaelRed]

Sorry, I just lept into the error of thinking datediff while clearly looking at dateadd.

MichaelRed
m.red@att.net

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