Query criteria multiple fields

[m17347047711116]

I want to query a table

Table has Order data as in the following sample:

[M1], [M2], [Order #], [Order Qty]

I want to query all the records in the table where
[M1] or [M2] is equal to 22

I know how to specify the criteria in the query for one field at a time, but I have never done it like this,

If I set the criteria in each field [M1] & [M2] as =22 then I only get the records where both fields are =22,
What I really want the query to return is all records where [M1] = 22 and
All the records where [M2] =22

below is a sample of some data to demonstrate:

[M1].....[M2]....[Order #]....[Qty]
[01].....[22].....[123456].....[100]
[22].....[86].....[123457 ]....[200]
[01].....[86].....[123458 ]....[300]
[22].....[22].....[123459 ]....[400]

EXAMPLE "A"
I want the query to return all the records in the table where [M1] & [M2] are 22,
The result on the above data I want is

[M1].....[M2]....[Order #]....[Qty]
[01].....[22].....[123456].....[100]
[22].....[86].....[123457 ]....[200]
[22].....[22].....[123459 ]....[400]


EXAMPLE "B"
The result I am getting when I set the criteria for [M1] =22 and [M2]=22
Is not as above, I am getting:

[M1].....[M2]....[Order #]....[Qty]
[22].....[22].....[123459 ]....[400]

Any idea how I can get Example A vs's B

 

[Mike375]

Put you 22 on the criteria row as you normally do for M1.

But for M2 put the 22 on the second criteria row.

When they are on the same row is is an And search and so will only retrieve records that have 22 in M1 and 22 in M2

But when you go the seond row it is an Or search and as such the search is saying...find all the records that have 22 in M1 and all the records that have 22 in M2.

You should see Or at the far left of the criteria row

 

[Brianwarnock]

In the design gris make sure the 2 fields criteria are on separate rows which will create the Where [M1]=22 Or [M2]=22 condition


brian

 

[Brianwarnock]

Its like buses , none for 25 minutes then 2 come along together.

brian

 

[Mike375]

Brian,

Given we rarely agree what were the chances of us both agreeing at the same time. This might need to go to the Atheist thread

 

[Brianwarnock]

I probably agree more often than you know, but that's no fun.

Brian

 

[m17347047711116]

Like a glove, that's hit the spot,
Thnx