I'm trying to write a query that finds any category that is not on of the ususal ones. so I have: SELECT Crinc.CENTER
FROM Crinc
WHERE (((Crinc.CENTER) Not Like "ER" And (Crinc.CENTER) Not Like "WR" And (Crinc.CENTER) Not Like "SE" And (Crinc.CENTER) Not Like "RO")) OR (((Crinc.CENTER) Is Null));
which works - Actually it's much longer but it works.

Then I realized I'd fogotten the date field so they user can select a month, six months etc so I added the date but then things got really weird. I can make this work:

SELECT Crinc.TRANS_DT, Crinc.CENTER, Crinc.BIL_NAME
FROM Crinc
WHERE (((Crinc.TRANS_DT) Between [Forms]![Crinc Query Menu]![startdate] And [Forms]![Crinc Query Menu]![EndDate]) AND ((Crinc.CENTER)<>"SE"));

Which gives me everything for the specified date range and no "SE"s in the [Center] field BUT I also lose all the records where [Center] is blank. So I added Is Null to the [Center] field and then I get ALL the dates, not just the ones I asked for. Yet in the first example above I have IS Null and a few other designations in the [Center] field and it works fine. Can anyone see what the problem is? It seems the Date 'Between' part is messing the rest of it up but I don't know why.

When you need to combine AND and OR conditions in the same WHERE clause, using the QBE can be confusing. Each row of criteria represents an AND condition so if you have more than one criteria in a single line they are ANDed (sic). Each row is ORed (sic). So to represent this criteria with the grid you would need the date criteria on each row. Then on one row the criteria for CENTER would be <> "SE" and on the other row it would be Is Null. Or you can just change the SQL code to:

SELECT Crinc.TRANS_DT, Crinc.CENTER, Crinc.BIL_NAME
FROM Crinc
WHERE (((Crinc.TRANS_DT) Between [Forms]![Crinc Query Menu]![startdate] And [Forms]![Crinc Query Menu]![EndDate]) AND ((Crinc.CENTER)<>"SE" or Crinc.Center Is Null));

Null is a special value and a test will only return true if it is specifically an Is Null test. In other words <> "SE" will return true for all fields that have some value (ie, they are not null) that is <> "SE". To find the ones that ARE null, you must test specifically for Null. I know this concept is confusing. I'll try again if I didn't clarify it for you.

Pat:It works, but it's still weird<grin>.

I did have Is Null on a separate line but it still didn't work. I started the reconstruction by pasting your code in the SQL part and then going back to design. I ran it and it worked so I started adding the rest and it added the 'Between' line twice in the date field column. If I remove one of them it doesn't work. Also I find if I start the column with Is Null, then OR, then AND the rest, it works. Though after running, the design grid puts Is NUll on a separate line. The differences seem subtle but it definately wants it in one kind of order rather than another.

Thanks a bunch - hopefully I learned something here <grin>.

All right, let me try again. It is important that you understand what is happening or you will NEVER be able to create complex criteria. The way one would write the criteria if coding SQL directly is (simplified for clarity)-
Where date between A AND B AND (field = "X" OR field = "Y")

Notice the parentheses around the OR condition. The parens FORCE the conditions to be evaluated the way you intend. ie the date part will evaluate to true or false and the field compare will evaluate to true or false and then the two results will be evaluated for true or false. In Boolean logic, the relational operators have an order of presidence much the way arithmetic operators (+,-,*,/) have an order of presidence in a mathmatical calculation. Parentheses are used to override the "natural" order which is what I showed in the sample above. Without the parens, the "natural" boolean order of evaluation for the above would be:

Where (date between A AND B AND field = "X") OR field = "Y"

I added the parens in the example above ONLY to show how the evaluation would proceed. Carefully compare the two statements and apply what you remember from high school algebra. Condition1 (everything within the parens) will be evaluated to true or false, then condition2 (field = "y") will be evaluated to true or false, then the two conditions will be evaluated and since the relational operator is OR, if either condition is true, the result is true.

Stay with me, we're getting there http://www.access-programmers.co.uk/ubb/smile.gif . Now, as to how the QBE grid works - each criteria row contains 1 complete condition which may have multiple parts. If there are multiple criteria lines, each criteria line is evaluated on its own merits and compared to subsequent criteria lines with the OR operator. So, if you used the QBE grid properly to build the criteria, what you would end up with is:

Where (date between A AND B AND field = "X") OR (date between A AND B AND field = "Y")

Notice that the first condition is repeated. This is because you want the first condition to be true in addition to either of the second conditions and so you would need to have placed it on two separate rows of critera. This statement will produce identical results to the shorthand version that I showed as the first example. The QBE grid produces the duplicate condition example because that is how you need to enter the conditions given the way the QBE interface is programmed to work.

I spent a lot of time with this explaination and would appreciate comments from anyone it helped http://www.access-programmers.co.uk/ubb/smile.gif Thanks

excellant explaination, best I've seen. Thank you very much. But after lookin gat it a few times, isn't:

Where (date between A AND B AND field = "X") OR (date between A AND B AND field = "Y")

incorrect? Doesn't this say between the dates and fields X & Y? Shouldn't there be another set of Parens separating the dates and the fields? It seems the 'between' refers to the beginning and ending date, then the X and Y are in addition to the between. How can 'between' be between two dates and another entity (number or character)?

Yes, in ref to your explanation, Pat, that was excellent. I had a similar problem recently where multiple AN/OR criteria was required including in that, a few 'ISNULLS'. I found by default, what you have explained so succinctly.

My QBEs compare and select data from 8 tables, and the biggest shortcoming in Access is stertching the field wide enough to put all the OR options for a particular field.

I use on average five lines of criteria, and copy and paste the ones to remain constant.
Great explanation!

rschultz, no they are not necessary because the Between relational operator takes two operands. If I were writing this clause for myself, I would surround the date parts with parens for readability but since the example was ABOUT the effects of parens, I left them out as they have NO impact on the evaluation of the statement and would only have confused the issue.

Pat -

As always a concise and easy to understand explanation that Microsoft would never have been able to achive. Do I see a book in your future? I hope so!

Thank you for sharing your expertise in a way that we can all understand.

Pat: Once again many thanks for claifying that.

I've since had to play with another one that was complex (for me anyway) and your explaination of that last one was a big help.

Thanks guys. Here's a link to another post I made on the topic of combining AND and OR conditions that you might want to read to solidify your understanding. It explains a little more about the presidence of various operators and also how the same thing applies in arithmetic calculations.
http://www.access-programmers.co.uk/ubb/Forum3/HTML/000289.html