Problem with using parameter in query with calculation from date

[MsT]

I have a database with a Date of Birth field. I have a query with a field that calculates the age from the Date of Birth (DateDiff("yyyy",[Date of Birth],Date())+Int(Format(Date(),"mmdd")

As a criteria in this field I want to be able to select a minimum age, so >=[please input minimum age]

However the results are bizarre - sometimes it gives the right answer, and sometimes not. It seems to have a particular problem with ages above 10, which show up all the time.

Could anyone help?

Thanks in advance

 

[MarkK]

Open the immediate pane and test your functions. For instance, this returns the number 101 . . .

? Int(Format(#1/1/2015#), "mmdd"))

. . . and then you add that to the age???
Testing expressions like this in a query is laborious, but it's super easy to tweak your expression and try it again in the immediate pane.

 

[apr pillai]

Create a separate Column in the Query with following expression:

Age:datediff("yyyy",[Date of Birth],Date())

below the Age column write the expression in the criteria row: >=[please input minimum age]

When the Query is run user will be able to input the criteria value to the parameter: [please input minimum age].

 

[MsT]

Argh, sorry, the end of the formula is cut off. The full formula is Age: DateDiff("yyyy",[Date of Birth],Date())+Int(Format(Date(),"mmdd")<Format([Date of Birth],"mmdd"))

The second part of the calculation corrects for ages where the birthday has not already passed in a given year

I don't think the formula is incorrect - the ages are calculated accurately. Using numbers in the criteria works too (e.g. <=9) Yet somehow, when the parameter is applied, it doesn't work consistently.

The calculation is a separate column in the query, with the parameter statement in the criteria row.

I am wondering if it's to do with the result of the calculation not being treated as a number? Although I have tried setting the format to number in the properties window.

 

[MsT]

Hi, I have solved it. I realised that the query was treating the parameter as text so I've put CInt in..
>=Cint([please enter minimum age])

Thanks everyone

 

[spikepl]

DateDiff does not work as you'd expect: http://www.sqlteam.com/article/datediff-function-demystified

I tend to use DateAdd when I can, thus on most occasions just applying the function on the opposite side of the equal sign.