the "or" function

[diagostj]

I'm making a query using a table of people's names and data. I'm trying to set up a query that creates a separate table where, out of three columns, if there is the number 2 in any two of those columns (whichever combination, it doesn't matter, just any two of the three), then that person will be added to the new table.

I know how to use the "or" specification on individual columns, but is there any way to do it over the course of three of them? Please let me know...


Please e-mail me if you have any suggestions

 

[samehkh]

I can really describe it in words, but if you look at the pic you will get it! remeber to remove the double quotes if your datatype is numeric.

 

[dcx693]

Use an expression like this to create a calculated column in a query. In the Field: line, enter:

Result: IIf(([Num1]=2 And [Num2]=2) Or ([Num1]=2 And [Num3]=2) Or ([Num2]=2 And [Num3]=2),True,False)

That formula works for numeric data types, you'll need to change it slightly if you have text. Just enter True into the Critiera: line beneath that new column and you'll only return those that have the number 2 in two of the three fields.

 

[Mile-O]

Or an APPEND query with the value of 2 put in different OR rows in the QBE.

 

[namliam]

I know its just another way of doint it but here is a oneliner:

instr(mid(Field1 & Field2 & Field3, instr(Field1 & Field2 & Field3,"2")+1),"2")

If this returns > 0 there are two 2's in the expresion.

Mind you if Field1 = "AAA2AAA2" it will also return >0 so i dont know if its what you want. In that regards above sollution is better.

Regards