Access 2003 query criteria issue....

[Steve P]

Hi everyone...I'm doing some simple data analysis (ie: no VBA) and found a
weird anomaly in the criteria field.

I’m using multiple criteria in a string field and want the query to select
(example) records that meet “criteria 1†OR “criteria 2â€. I’ve found that
the query returns different results when the whole criteria (situation 1 and
2) are put on the same line in the query builder, as opposed to writing the
first criteria on one line and the second on the line below….does this make
sense??? Why would I be getting 2 different outputs if both ways imply “OR�

Please advise…

 

[Allen Browne]

Say you use criteria like this:
Field: Student Result
Criteria: "Fred" 1 or 2
This query returns only the records where Fred's result is 1 or 2.

Now if you use:
Field: Student Result
Criteria: "Fred" 1
Or: 2
This query returns the records where Fred scored a 1, or where *anyone*
scored a 2 (because the Student criterion is blank on the 2nd row.)

If you examing the WHERE condition of the SQL statement, the first one above
will be:
WHERE Student = "Fred" AND (Result = 1 OR Result = 2)
The second one will be:
WHERE (Student = "Fred" AND Result = 1) OR Result = 2
The bracketing is *really* important: you get different results if you
perform the AND first and then the OR, or if you perform the OR first and
then the AND.

(Access tends to add additional brackets that aren't meaningful: I've just
showed the important brackets in the example above.)

 

[John W. Vinson]

I’ve found that
the query returns different results when the whole criteria (situation 1 and
2) are put on the same line in the query builder, as opposed to writing the
first criteria on one line and the second on the line below….does this make
sense??? Why would I be getting 2 different outputs if both ways imply “OR”?

Because it is NOT the case that both ways imply OR.

Two criteria on the same line use AND.
Two criteria on different lines use OR.

Try it in the query grid; switch to SQL view and you'll see.

 

[Marshall Barton]

Hi everyone...I'm doing some simple data analysis (ie: no VBA) and found a
weird anomaly in the criteria field.

I’m using multiple criteria in a string field and want the query to select
(example) records that meet “criteria 1” OR “criteria 2”. I’ve found that
the query returns different results when the whole criteria (situation 1 and
2) are put on the same line in the query builder, as opposed to writing the
first criteria on one line and the second on the line below….does this make
sense??? Why would I be getting 2 different outputs if both ways imply “OR”?

That's the way it is supposed to work.

Conditions in a criteria row are ANDed together. Then the
separate rows are ORed together. An indicator of this is
the word Or at the left of the second criteria row,

If you find that too confusing, you should use SQL view to
enter the entire WHERE clause so you can enter the real
criteria with ANDa and ORs with parenthesis just the way you
want them. If you do this, be aware that switching back to
design view will change things to an equivalent combination
of calculated fields, ANDs, ORs and parenthesis that you may
not recognize.