query won't work

[rschultz]

I have a query:
SELECT Crinc.TRANS_DT, Crinc.CENTER, Crinc.BIL_NAME, Crinc.ROUTE, Crinc.PROD_LIST, Crinc.CARRIER, Crinc.WEIGH_NET
FROM Crinc
WHERE (((Crinc.TRANS_DT) Between [Forms]![Crinc Query Menu]![startdate] And [Forms]![Crinc Query Menu]![EndDate]) AND ((Crinc.CENTER) Not Like "ER" And (Crinc.CENTER) Not Like "WR" And (Crinc.CENTER) Not Like "SE" And (Crinc.CENTER) Not Like "RO" And (Crinc.CENTER) Not Like "NR")) OR (((Crinc.TRANS_DT) Between [Forms]![Crinc Query Menu]![startdate] And [Forms]![Crinc Query Menu]![EndDate]) AND ((Crinc.BIL_NAME) Not Like "*Scott*" And (Crinc.BIL_NAME) Not Like "*Science*" And (Crinc.BIL_NAME) Not Like "*Mesa*" And (Crinc.BIL_NAME) Not Like "Diamond*" And (Crinc.BIL_NAME) Not Like "Fred*" And (Crinc.BIL_NAME) Not Like "*Nutrition" And (Crinc.BIL_NAME) Not Like "*LadLaw*"));

but it won't do the first part. I still get records that have ER etc. in the

field.

If I change the OR to another AND (that is, I leave the [Bil_Name] requirements on the same line in the Query Design screen):
SELECT Crinc.TRANS_DT, Crinc.CENTER, Crinc.BIL_NAME, Crinc.ROUTE, Crinc.PROD_LIST, Crinc.CARRIER, Crinc.WEIGH_NET
FROM Crinc
WHERE (((Crinc.TRANS_DT) Between [Forms]![Crinc Query Menu]![startdate] And [Forms]![Crinc Query Menu]![EndDate]) AND ((Crinc.CENTER) Not Like "ER" And (Crinc.CENTER) Not Like "WR" And (Crinc.CENTER) Not Like "SE" And (Crinc.CENTER) Not Like "RO" And (Crinc.CENTER) Not Like "NR") AND ((Crinc.BIL_NAME) Not Like "*Scott*" And (Crinc.BIL_NAME) Not Like "*Science*" And (Crinc.BIL_NAME) Not Like "*Mesa*" And (Crinc.BIL_NAME) Not Like "Diamond*" And (Crinc.BIL_NAME) Not Like "Fred*" And (Crinc.BIL_NAME) Not Like "*Nutrition" And (Crinc.BIL_NAME) Not Like "*LadLaw*"));

I don't get any data for results, but I know there are about 150 records that don't meet any of the requirements. From what I understand the first way should work. Can anyone see what's wrong?​

 

[Alexandre]

Not like "*ER*"

Alex

 

[KKilfoil]

Quote:
"but it won't do the first part. I still get records that have ER etc. in the

field."


Watch your parenthesese. Center <>"ER" needs to be AND'ed with (All of the rest of your crtieria) if you never want to see 'ER' in Center (or *ER* as Alexandre has pointed out).

If I read your code right, your bit after the 'OR ' does not also exclude Center='ER'​

 

[KKilfoil]

If you are looking at your query on the design grid, I suspect you did not put

<> "ER" on EACH criteria line​

 

[rschultz]

>>If you are looking at your query on the design grid, I suspect you did not put

<> "ER" on EACH criteria line <<

but if that's true, then the first one should work with the [Bil_Name] and the

requirements on the same line shouldn't it? Why even have an OR statement then?​

 

[KKilfoil]

I may be restating the obvious, but...

In the query design grid, all criteria entered on the same line are ANDed, so that every criteria must be true for a record to show up in the query result.

Criteria on different lines in the grid are ORed, but a 'whole line' at a time! That is, if a record satisfies ALL of the criteria on ANY ONE line, it will appear in the query results, even if it fails one criterion on a different line.


In your query, as best as I can tell, you are excluding some records in your 'CENTER' field on one line, and excluding some records in 'BIL_NAME' on another.

So you are telling Access to include all records that are not excluded by just the first line, AND include all records that are not excluded by just the second line.

When you are using boolean logic to EXCLUDE rather than include records, AND and OR work 'backwards'.

I find it easier (more intuitive) to write a query that INCLUDES every record I want to actually exclude, and then use a second query to find all of the records in my original datasource that does not appear in my 'exclusion' query (The 'Find Unmatched Query Wizard' makes this simple.)

[This message has been edited by KKilfoil (edited 02-22-2002).]

 

[rschultz]

KKilfoil:thanks for confirming what I thought I knew. I thought about your idea to have query that includes what I'm looking for but one of the things the query is doing is filtering out typos, among other errors. If the totals don't match they can run the "other" query and see that e.g "Rolloff" shouldn't be in "SE" and that's why the numbers are off. I thought it was working at one time but now it's not.
Hold the phone.. this works.

SELECT Crinc.TRANS_DT, Crinc.CENTER, Crinc.BIL_NAME, Crinc.PROD_LIST, Crinc.WEIGH_NET
FROM Crinc
WHERE (((Crinc.TRANS_DT) Between [Forms]![Crinc Query Menu]![startdate] And [Forms]![Crinc Query Menu]![EndDate]) AND ((Crinc.CENTER) Is Null) AND ((Crinc.BIL_NAME) Not Like '*Scott*' And (Crinc.BIL_NAME) Not Like '*Science' And (Crinc.BIL_NAME) Not Like '*Mesa*' And (Crinc.BIL_NAME) Not Like 'Diamond*' And (Crinc.BIL_NAME) Not Like 'Fred*' And (Crinc.BIL_NAME) Not Like '*Landfill*' And (Crinc.BIL_NAME) Not Like '*Nutrition' And (Crinc.BIL_NAME) Not Like '*LadLaw*')) OR (((Crinc.TRANS_DT) Between [Forms]![Crinc Query Menu]![startdate] And [Forms]![Crinc Query Menu]![EndDate]) AND ((Crinc.CENTER)<>'SE' And (Crinc.CENTER)<>'NR' And (Crinc.CENTER)<>'RO' And (Crinc.CENTER)<>'WR' And (Crinc.CENTER)<>'ER'));

I'll have to look at it closer, but it looks like if I put "is null" on the first line of the

field and the all teh [Bil_Name] stuff on teh same line, and the date stuff too THEN I put the rest of teh [Cenbter] stuff on teh next line with identical date field stuff it works. Toooo weird.​