Between/And parameter not working

[Lowell]

I have searched for an answer but to no avail.



I have a query to pull up DOB for a person and give them an age. That works perfect.

I want to use that query for serveral different reports.

Then I made a query running off the age query to bring up user selected ages.

If I put in "2" it brings up 2, 21, etc.

If I put in "6" it brings up 6, 65, etc.

I guess I have done something wrong?

I use this parameter for my age:

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

I used this parameter for between/and:

Between [Start Age] And [End Age]

Help?

[Bob Fitz]

I think you need:

Between CInt([Start Age]) And CInt([End Age])

[RayMilhon]

Bob's Suggestion is the best way to accomplish what you want. However, I would add one other thing.

Age: CINT(DateDiff("yyyy",[BirthDate],Date())+Int(Format(Date(),"mmdd")<Format([BirthDate],"mmdd")))

This gaurantees the field and the Criteria to be the same datatype.

Also I am curious Do you use the " when you enter the age? When you use Criteria in a query as you do Access assumes a Variant Datatype. If you're including the " when you enter the "start age" and "end age" Access determines it's a string which would account for the results you're receiving. "6", "60", "69" are all between "6" and "7"

[Lowell]

I have changed my parameter and it works good as far as I can tell. I never knew that option existed. Many thanks to you for taking the time to help me. That's why I keep reading this forum, searching this forum, and posting my questions.

About the last question Ray, I don't use the quotations. After I had posted the question I realized it looked a little odd, but hoped my point would still get across. Thanks for mentioning it though.

Good day.

[RayMilhon]

Honestly, I didn't think you did but after 20 years doing this I've learned to validate everything, assume nothing.