weirdness

[rschultz]

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

field BUT I also lose all the records where

is blank. So I added Is Null to the

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

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.​

 

[rschultz]

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>.

 

[rschultz]

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)?

 

[Rich_Lovina]

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!

 

[Jack Cowley]

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.

 

[rschultz]

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.