Age query from date of birth field

[chris.mo]

Please help someone!!

I'm trying to do a parameter query on age...something like Between 19 and 22 to show all goalkeepers in that age range. The thing is I have only Date of birth in the Goalkeepers table.

I thought I could do a query with a simple expression to calculate age, then do a parameter query from that query.

I had some success playing with the following:

Expr1: (Date()-[Goalkeepers]![Date of birth])/365.33

...Simply subtracting the DOB date field from the current date then dividing by 365.33. This gives the right age, but with a load of decimal places I cant get rid of even by setting the decimal places to 0 in the format property box??

R. Hicks gave a long VB script but I don't know VB atall. (see topic below)

I might be just ignorant here but isnt there a shorter way than he suggests? I dont wish to appear ungrateful but im a bit cconfused.

Chris.

 

[Jack Cowley]

Try this in your query:

Age: DateDiff("yyyy",[Date of birth],Now())+Int(Format(Now(),"mmdd")<Format([Date of birth],"mmdd"))

In the Criteria field of this column use Between 19 and 22.

 

[chris.mo]

Thanks Jack, It works perfectly.

Chris Polhill,
Liverpool, England.