Stumped by query results

[Richardson]

I have just about torn my hair out over strange results from a query and I
am looking for any explanation possible.
The query has a field, dateofbirth and a field, age, that calculates from
date of birth. The formula is
datediff("yyyy",[dateofbirth],date())
+Int(format(date(),"mmdd")<format([dateofbirth],"mmdd")).
It calculates the age correctly.
I need to filter by this field for people between certain ages. If I type
the criteria Between 24 and 38 I get the right data.
When I make it a parameter Between [A] and it returns completely
different values, like a 3 year old.
I don't understand why it would give different results from typing the value
to using a parameter.

The strangest part is that it works in an older copy, but not here and it
has not changed.
I tried deleting the query and copying it from the copy that worked and it
still didn't work right.

Any suggestions,please?

Thanks,
Lori

 

[John Vinson]

When I make it a parameter Between [A] and it returns completely
different values, like a 3 year old.
I don't understand why it would give different results from typing the value
to using a parameter.

Define the Parameters as Numbers and you may have better luck. I
suspect it's treating them as text strings.

Right mouseclick the background of the tables and select Parameters;
put [A] and in the left column and specify Number in the right.

 

[Richardson]

Thank you, that worked. Since I had it set and working right in the
database and then copied it from a desktop to a laptop with the same
software, Office Xp Developers, I didn't think to check that again. Can
you explain to me why it understood it as a number in one place and as a
string when it was copied? Is that something I need to watch for in all my
databases that I am transferring over to the new laptop?

Lori

When I make it a parameter Between [A] and it returns completely
different values, like a 3 year old.
I don't understand why it would give different results from typing the value
to using a parameter.

Define the Parameters as Numbers and you may have better luck. I
suspect it's treating them as text strings.

Right mouseclick the background of the tables and select Parameters;
put [A] and in the left column and specify Number in the right.