View Full Version : OR expression help


arage
12-19-2001, 06:24 AM
OR expression help

I setup a query to receive multiple criteria against at least one of 3 fields.

My criteria are the name of controls on a form, 3 controls total.

I specified different combinations of my criteria (controls) in the query grid, 7 rows total.
In 3 rows, I specified a combination of 2 of the controls only. The 4th row has all 3 parms included and the last 3 rows have a single parm, one for each field.

Access says it “connects criteria in different rows with OR, and finds records that meet the criteria for the first or second row of the design grid.”

If this is the case why do I get incorrect results? specifically for rows where only 2 parms are specified?

Pat Hartman
12-19-2001, 10:03 AM
You may not be able to get the query to work properly using this technique. I'll use just two rows for simplicity.

A and B
or
A and B and C

If you want rows returned where A is true and B is true and the value of C is irrelevant then this will work but having the second set of conditions is pointless since the value of C will NEVER impact the decision to select any given row. Once A and B are both true, the record satisfies one of the OR conditions and that is enough for it to be selected.

The following is the Where clause from a query that I use that allows optional parameters:

WHERE
((E.UWRespName = Forms!frmFilter!txtHiddenUserId
Or Forms!frmFilter!txtHiddenUserId Is Null)<>False)

AND ((E.DataEntryCompleteFlg=Forms!frmFilter!txtHidden Work Or Forms!frmFilter!txtHiddenWork Is Null)<>False);

The query is used to filter rows in a combobox. If a user name is selected, only his data is shown. The OR part of the first contdition allows for the posibility that no specific filtering by user name is desired. The second condition is similar but for a different field. Notice how in both cases the OR part of the condition checks the form field for null.