Solved Criteria equivalent to BUT (1 Viewer)

[AnilBagga]

I have a query with 2 fields on which I want to add a filter criteria

Invoice Unit - Exclude data with value M2N - criteria is <>"M2N" - works

BUT I dont want this filter to work for 3 Customer Codes - say A,B and C.

Meaning filter condition is filter out all records where Invoice Unit is M2N BUT not for customer codes where Invoice unit is M2N AND CustomerCodes are A,B or C

How does one achieve this?

 

[Isaac]

use two of the Criteria lines/rows.

one to cover each criteria

 

[AnilBagga]

use two of the Criteria lines/rows.

one to cover each criteria

Can you explain further. What criteria do I add in the Customer Code field. There are hundreds of Customer Codes in the tables. I cannot put "A" and "B" and "C" in the customer code criteria as this will remove all other Customers from the data!

 

[MajP]

WHERE SomeField <> "M2N" Or SomeField = "A" Or SomeField = "B" or SomeField = "C"
In the Designer you can do this on each line of the criteria since each line is an or

<> "M2N"
"A"
"B"
"C"

 

[Pat Hartman]

Where Somefield <> "M2N" and SomeOtherField NOT In("A", "B", "C")

 

[AnilBagga]

M2N is a seperate field called InvoiceUnit

A, B and C are of another field called CustomerCode

This is the problem

 

[MajP]

Then it looks like this in the designer. Notice field 2 begins a row below.

Field 1       Field 2

<> M2N
                   A
                   B
                   C

WHERE SomeField <> "M2N" Or SomeOtherField = "A" Or SomeOtherField = "B" or SomeOtherField = "C"

 

[AnilBagga]

You mean A, B, C will be in Or rows under column SomeOtherField

 

[AnilBagga]

Then it looks like this in the designer. Notice field 2 begins a row below.

Field 1       Field 2

<> M2N
                   A
                   B
                   C

WHERE SomeField <> "M2N" Or SomeOtherField = "A" Or SomeOtherField = "B" or SomeOtherField = "C"

Worked!

 

[Pat Hartman]

Did you try my suggestion? Using the qbe, both go on the same line so they are and'd

 

[AnilBagga]

This is how the QEB looks now and is giving the desired results.

All records where MewarInvUnit = M2N are excluded except if ConsigneeCode = 1048, 1091...or 360167

Records where MewarInvUnit is <> M2N and Consignee Code = 362389 or 360631 are excluded

 

[Pat Hartman]

That isn't what you asked for originally. What you have now only looks at MewarInvUnit when ConsigneeCode is not one of the two on the first row. The other rows ignore Unit entirely. That may be what you want but creating queries with hardcoded references to primary keys is not useful. You are much better off using criteria that is supplied by fields on a form.

Also if ConsigneeCode is numeric, do not enclose it with double quotes.

 

[AnilBagga]

That isn't what you asked for originally. What you have now only looks at MewarInvUnit when ConsigneeCode is not one of the two on the first row. The other rows ignore Unit entirely. That may be what you want but creating queries with hardcoded references to primary keys is not useful. You are much better off using criteria that is supplied by fields on a form.

Also if ConsigneeCode is numeric, do not enclose it with double quotes.

Mr Hartman

I want all rows under Consignee Code to also use the filter condition os MewarInvUnit. As I stated before the condition translated should be all records where MewarInvUnit <> M2N but if customer codes are A,B,C , include them and ignore the MewarUnit filter condition. Howw can this be achieved?

 

[Pat Hartman]

For the second time - did you try my suggestion? Look back at post #5

 

[AnilBagga]

Where Somefield <> "M2N" and SomeOtherField NOT In("A", "B", "C")

Mr Hartman

Is the screen shot what you suggest?

I think I got got wrong . This query removes all records with MewarInvUnit = M2N.
Records with MewarInvUnit = M2N and ConsigneeCode say 1091 are also removed. But we dont want these 6 customer codes to be filtered out!

I used this query by entering this sequence - Not In (..) in the "Or" row too - Same result!

 

[MajP]

That created the string
<> "M2N" AND NOT IN ("1048"....."360167")
I doubt that is what you want. You want an OR conditions

<> "M2N" OR IN ("1048"....."360167")

<> "M2N"
                   IN (1048"....."360167")

To be clear this is the same as previously done in post #7 only using an OR instead of an IN.

 

[AnilBagga]

That created the string
<> "M2N" AND NOT IN ("1048"....."360167")
I doubt that is what you want. You want an OR conditions

<> "M2N" OR IN ("1048"....."360167")

<> "M2N"
                   IN (1048"....."360167")

To be clear this is the same as previously done in post #7 only using an OR instead of an IN.

Thank you. I was not aware of the IN function

 

[Pat Hartman]

We don't know the actual conditions and it appears that they are not clear to you either. I've shown you a way to use the In() function with AND and MajP expanded the explanation to show how to use OR instead of AND when using the QBE.

 

[AnilBagga]

Pat/MajP

I have used the In function exactly as suggested. It works perfectly! Thank you both for your help

Anil