Between Age incorrectly returns 100+ yr olds!

[Guest]

I have a query called [AGECALC] that calculates the age of all entries in
table [MEMBERS]. It does this correctly using the common datediff formula
that everyone uses to calculate age.


I have a query [AGES BETWEEN] that searches for entries in query [AGECALC]
between a certain age range.
I have the following as the criteria for AGE
Between [Enter Minimum Age:] And [Enter Maximum Age:]

This query, if I enter the values 0 and 50 or 1 and 50, it returns everyone
between those ranges plus everyone 100 years old and above.

This doesn't happen if I search for "between 2 and 50"

Furthermore, if I enter the values 50 and 100, it asks me again to enter
both [Enter Minimum Age:] and [Enter Maximum Age:]. If I enter the same
values, it returns everyone between those values, plus everyone over 100.

Any ideas?? I have gone through so many posts in both this forum and
others. Believe me, I wouldn't ask the question otherwise. I really
appreciate any ideas that are offered! Thanks!

 

[Guest]

Hi

Could you post the SQL of both queries

 

[John W. Vinson]

I have a query called [AGECALC] that calculates the age of all entries in
table [MEMBERS]. It does this correctly using the common datediff formula
that everyone uses to calculate age.


I have a query [AGES BETWEEN] that searches for entries in query [AGECALC]
between a certain age range.
I have the following as the criteria for AGE
Between [Enter Minimum Age:] And [Enter Maximum Age:]

This query, if I enter the values 0 and 50 or 1 and 50, it returns everyone
between those ranges plus everyone 100 years old and above.

Sounds like AGE is being calculated as a Text field. The text string "100" -
or for that matter "10225" - is "between" "1" and "2".

Perhaps you could post the SQL of AGECALC and of AGES BETWEEN.

John W. Vinson [MVP]

 

[Guest]

Actually, that is what I posted above. I'll post the SQL again in case my
previous post isn't showing up for you.

Here is agecalc:
SELECT MEMBERS.*,
DateDiff("yyyy",[DOB],Date())+(Format([DOB],"mmdd")>Format(Date(),"mmdd"))
AS AGE, IIf([CLRECNO]>0,1) AS Justone
FROM MEMBERS;

Here is the BetweenAges query:
SELECT *
FROM [AGE CALC]
WHERE ((([AGE CALC].AGE) Between [Enter Minimum Age:] And [Enter Maximum
Age:]));

Thanks everyone!

 

[John W. Vinson]

Actually, that is what I posted above. I'll post the SQL again in case my
previous post isn't showing up for you.

Sorry! Read the post too hastily.

That's really odd. The symptom is certainly consistant with the AGE field
being treated as text, but there's nothing in the SQL that would cause it.

Just for curiosity (as a belt-and-braces getaround) try

SELECT *
FROM [AGE CALC]
WHERE Val([AGE CALC].AGE) Between [Enter Minimum Age:] And [Enter Maximum
Age:];

to explicitly force AGE to a numeric datatype.

There isn't a separate AGE field in the table, I hope...?

John W. Vinson [MVP]

 

[Guest]

Hi

John is right. The between clause is treating the ages as text.

It's the parameters that are making it do that and I got it to work by
enclosing the parameters with cint() like this...

Query: BetweenAges...

SELECT *
FROM [AGE CALC]
WHERE ((([AGE CALC].AGE) Between cint([Enter Minimum Age:]) And cint([Enter
Maximum Age:])));

hth

Andy Hull

Actually, that is what I posted above. I'll post the SQL again in case my
previous post isn't showing up for you.

Here is agecalc:
SELECT MEMBERS.*,
DateDiff("yyyy",[DOB],Date())+(Format([DOB],"mmdd")>Format(Date(),"mmdd"))
AS AGE, IIf([CLRECNO]>0,1) AS Justone
FROM MEMBERS;

Here is the BetweenAges query:
SELECT *
FROM [AGE CALC]
WHERE ((([AGE CALC].AGE) Between [Enter Minimum Age:] And [Enter Maximum
Age:]));

Thanks everyone!

Sounds like AGE is being calculated as a Text field. The text string "100" -
or for that matter "10225" - is "between" "1" and "2".

Perhaps you could post the SQL of AGECALC and of AGES BETWEEN.

John W. Vinson [MVP]

 

[John W. Vinson]

Hi

John is right. The between clause is treating the ages as text.

It's the parameters that are making it do that and I got it to work by
enclosing the parameters with cint() like this...

Query: BetweenAges...

SELECT *
FROM [AGE CALC]
WHERE ((([AGE CALC].AGE) Between cint([Enter Minimum Age:]) And cint([Enter
Maximum Age:])));

Good point!!!

Or alternatively use the Parmeters() expression:

PARAMETERS [Enter Minimum Age:] Integer, [Enter Maximum Age] Integer;
SELECT *
FROM [AGE CALC]
WHERE ((([AGE CALC].AGE) Between [Enter Minimum Age:] And [Enter Maximum
Age:]));

John W. Vinson [MVP]

 

[Guest]

Thanks to everyone for their help!! I read these discussions all the time,
but never really have the knowledge to contribute. So thank you very much
for helping me out!

 

[Guest]

Dang. Alright, guys. Earlier I clicked that your replies were helpful
because they were.

They unfortunately didn't actually work. The CInt method works "the best".
When I run the query, it asks me again to input the numbers, and if I input
them twice, it works! But why does it ask twice? That looks quite
unprofessional.

The other two methods given to me act strangely.
When I run those queries, sometimes it asks for "Enter minimum age:" and
"Enter maximum age:" , and then it asks for "Enter:" Sometimes they will
ask for "Enter:" in between asking for the other two.

Where is this strange behavior coming from??
There is no additional SQL- I'm not hiding anything!

 

[John W. Vinson]

The other two methods given to me act strangely.
When I run those queries, sometimes it asks for "Enter minimum age:" and
"Enter maximum age:" , and then it asks for "Enter:" Sometimes they will
ask for "Enter:" in between asking for the other two.

Where is this strange behavior coming from??
There is no additional SQL- I'm not hiding anything!

Use Tools.... Database Utilities... Compact and Repair for starters just in
case the database has become corrupted with all the changes you're making.

Then post the fieldnames and datatypes of your table, and the current SQL of
the queries. There must be a parameter [Enter:] *somewhere* or it wouldn't be
prompting for it. If a query calls another query, it might be in either query
and hence not all that visible!

John W. Vinson [MVP]

 

[Guest]

I compacted and repaired, and checked both my [Age Calc] and [Between ages]
queries for any changes or oddities.

Here's what's really weird. When I run the query [between ages] it asks me
twice to enter both the minimum and maximum values.

When I run a report based on that query, it only asks once for each, and
works perfectly. It does however take ~7 seconds from the time i double
click that report until the time it asks me for the first input. Nothing
else takes anywhere near that much time to open and process in my database.
I'm on a dual core 2ghz processor here. I restarted my computer, tried
again, and same thing.

So.. the report based on the query works fine, but the query asks twice for
each input. Oh, Access.

Any ideas? Thanks again for the help!

 

[Guest]

Hi again

I did a search of this group for "parameter twice" and it seems that this
problem can occur if you run a query - sort or filter it in the results view
- and then save it.

Try the following...
Rename both queries (to something like qryName_old)
For each query in turn...
Go into SQL view
Copy the SQL
Create a new query and go into SQL view (without adding any tables)
Paste the SQL
Save query (with correct name)

If they work delete the old ones.

hth

Andy Hull