Weird Query Results.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I swear I must be losing my mind, because for the life of me, I cannot get a
simple querry to work. I am using the following criteria in my query for 3
fields:
=([Forms]![Offset]![Text11]-[Forms]![Offset]![Text14]) And <=([Forms]![Offset]![Text11]+[Forms]![Offset]![Text14])
Click to expand...
=([Forms]![Offset]![Text13]-[Forms]![Offset]![Text15]) And <=([Forms]![Offset]![Text13]+[Forms]![Offset]![Text15])
Click to expand...

[Forms]![Offset]![Text19]

and for whatever reason, it provides me with values outside these ranges. I
have tried reducing each to only the >= requirement, and it works, but the
minute I add the <= , the query fails to yield correct results. Using <=
fails on its own also fails. It's almost as though it is treating each
criteria as an Or. If it fails the first or second, but passes the third,
its a valid result, etc. This one is driving me crazy because the query is
so simple, yet I can't get it to work. Any help would be much appreciated,
and likely to preserve my sanity. Thanks!
 
Several things could cause Access to misunderstand the data.

1. Unbound controls
If the text boxes are unbound, Access doesn't know what data type they
contain, and it may perform the wrong kind of comparison (such as string
comparison instead of numeric.)
To avoid this, set the Format property of all the text boxes to:
General Number
or similar.

2. Undeclared parameters
In query design choose Parameters on the Query menu, and enter a row for
each of the parameters. Assuming these text boxes contain whole numbers, you
will end up with rows like this:
Forms]![Offset]![Text11] Long
Forms]![Offset]![Text14] Long
Forms]![Offset]![Text13] Long
Forms]![Offset]![Text15] Long
Again this will ensure the comparison is numeric.

3. Field type
What is the Data Type of the field you placed this criteria under?
If you open your table in design view, is it a Number, not Text?
It makes a difference.
If the field is actually a calculated field, typecast the calculation.
Details in:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

4. Text box not yet updated.
If the focus is still in one of the text boxes, its Value will not have been
updated yet. That means the query will not be seeing the same value that you
see in the text box. To avoid this, make sure the focus has moved out of the
last text box you edited before running the query.

5. Nulls
If any of the text boxes is left blank, its value will be Null. The result
of the comparison will then be Null, and since it is not True the records
won't match.
Details in:
The Query Lost My Records!
at:
http://allenbrowne.com/casu-02.html

Could be other things, but hopefully that's got you covered.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

godadj said:
I swear I must be losing my mind, because for the life of me, I cannot get
a
simple querry to work. I am using the following criteria in my query for
3
fields:
=([Forms]![Offset]![Text11]-[Forms]![Offset]![Text14]) And
<=([Forms]![Offset]![Text11]+[Forms]![Offset]![Text14])
Click to expand...
=([Forms]![Offset]![Text13]-[Forms]![Offset]![Text15]) And
<=([Forms]![Offset]![Text13]+[Forms]![Offset]![Text15])
Click to expand...

[Forms]![Offset]![Text19]

and for whatever reason, it provides me with values outside these ranges.
I
have tried reducing each to only the >= requirement, and it works, but the
minute I add the <= , the query fails to yield correct results. Using <=
fails on its own also fails. It's almost as though it is treating each
criteria as an Or. If it fails the first or second, but passes the third,
its a valid result, etc. This one is driving me crazy because the query
is
so simple, yet I can't get it to work. Any help would be much
appreciated,
and likely to preserve my sanity. Thanks!
Click to expand...
 
Thanks Allen, worked like a charm!


Allen Browne said:
Several things could cause Access to misunderstand the data.

1. Unbound controls
If the text boxes are unbound, Access doesn't know what data type they
contain, and it may perform the wrong kind of comparison (such as string
comparison instead of numeric.)
To avoid this, set the Format property of all the text boxes to:
General Number
or similar.

2. Undeclared parameters
In query design choose Parameters on the Query menu, and enter a row for
each of the parameters. Assuming these text boxes contain whole numbers, you
will end up with rows like this:
Forms]![Offset]![Text11] Long
Forms]![Offset]![Text14] Long
Forms]![Offset]![Text13] Long
Forms]![Offset]![Text15] Long
Again this will ensure the comparison is numeric.

3. Field type
What is the Data Type of the field you placed this criteria under?
If you open your table in design view, is it a Number, not Text?
It makes a difference.
If the field is actually a calculated field, typecast the calculation.
Details in:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

4. Text box not yet updated.
If the focus is still in one of the text boxes, its Value will not have been
updated yet. That means the query will not be seeing the same value that you
see in the text box. To avoid this, make sure the focus has moved out of the
last text box you edited before running the query.

5. Nulls
If any of the text boxes is left blank, its value will be Null. The result
of the comparison will then be Null, and since it is not True the records
won't match.
Details in:
The Query Lost My Records!
at:
http://allenbrowne.com/casu-02.html

Could be other things, but hopefully that's got you covered.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

godadj said:
I swear I must be losing my mind, because for the life of me, I cannot get
a
simple querry to work. I am using the following criteria in my query for
3
fields:
=([Forms]![Offset]![Text11]-[Forms]![Offset]![Text14]) And
<=([Forms]![Offset]![Text11]+[Forms]![Offset]![Text14])
Click to expand...
=([Forms]![Offset]![Text13]-[Forms]![Offset]![Text15]) And
<=([Forms]![Offset]![Text13]+[Forms]![Offset]![Text15])
Click to expand...

[Forms]![Offset]![Text19]

and for whatever reason, it provides me with values outside these ranges.
I
have tried reducing each to only the >= requirement, and it works, but the
minute I add the <= , the query fails to yield correct results. Using <=
fails on its own also fails. It's almost as though it is treating each
criteria as an Or. If it fails the first or second, but passes the third,
its a valid result, etc. This one is driving me crazy because the query
is
so simple, yet I can't get it to work. Any help would be much
appreciated,
and likely to preserve my sanity. Thanks!
Click to expand...
Click to expand...
 
Back
Top