Query using Like

[mattc66 via AccessMonster.com]

Hi All,

I have a query that is based on a list of numbers. The first column has codes:

101
102
103
108
109
111
124

I want to use the LIKE 102 and LIKE 124. If I do the and LIKE the query is
blank, but if I do the LIKE 102 it shows the data for 102 and if I do a LIKE
124 it shows the Data for 124. Why doesn't it show the data when I do them
together?

 

[Jerry Whittle]

Replace AND with OR

 

[John W. Vinson]

Hi All,

I have a query that is based on a list of numbers. The first column has codes:

101
102
103
108
109
111
124

I want to use the LIKE 102 and LIKE 124. If I do the and LIKE the query is
blank, but if I do the LIKE 102 it shows the data for 102 and if I do a LIKE
124 it shows the Data for 124. Why doesn't it show the data when I do them
together?

Why are you using LIKE at all? The only function for the LIKE operator is to
use wildcards when searching a variable text string: for instance, Like "M*"
will find all text strings beginning with M. If you don't have a wildcard in
your query, LIKE works the same way as = (although probably it's slower).

As for the AND in your query - AND and OR are English language conjunctions,
but they are also Boolean algebra operators. They look the same, and in some
senses they are synonyms... but they are different.

[Field] = 102 AND [Field] = 124

will evaluate two expressions to see if they are TRUE or FALSE. Let's say the
field contains 102. The first expression will be TRUE; the second will be
FALSE (102 is not equal to 124 after all).

The AND operator returns TRUE if and only if both arguments are TRUE: you have
TRUE AND FALSE

so the result is FALSE (the number in FIELD cannot be equal to 102 and also
equal to 124 at the same time).

If you want the records containing 102 and 124 to both be retreived use OR:
the Boolean operator OR returns TRUE if either or both of its arguments are
TRUE.

[Field] = 102 OR [Field] = 124

will evaluate TRUE OR FALSE - since one of the arguments is true, the whole
expression is true.


John W. Vinson [MVP]

 

[mattc66 via AccessMonster.com]

The Or worked. Thanks for the additional information on the LIKE operator. I
didn't know that.

Hi All,

[quoted text clipped - 12 lines]

124 it shows the Data for 124. Why doesn't it show the data when I do them
together?

Why are you using LIKE at all? The only function for the LIKE operator is to
use wildcards when searching a variable text string: for instance, Like "M*"
will find all text strings beginning with M. If you don't have a wildcard in
your query, LIKE works the same way as = (although probably it's slower).

As for the AND in your query - AND and OR are English language conjunctions,
but they are also Boolean algebra operators. They look the same, and in some
senses they are synonyms... but they are different.

[Field] = 102 AND [Field] = 124

will evaluate two expressions to see if they are TRUE or FALSE. Let's say the
field contains 102. The first expression will be TRUE; the second will be
FALSE (102 is not equal to 124 after all).

The AND operator returns TRUE if and only if both arguments are TRUE: you have
TRUE AND FALSE

so the result is FALSE (the number in FIELD cannot be equal to 102 and also
equal to 124 at the same time).

If you want the records containing 102 and 124 to both be retreived use OR:
the Boolean operator OR returns TRUE if either or both of its arguments are
TRUE.

[Field] = 102 OR [Field] = 124

will evaluate TRUE OR FALSE - since one of the arguments is true, the whole
expression is true.

John W. Vinson [MVP]

 

[Jerry Whittle]

Since you don't need the wild card, there's an even nicer way to write the
statement.

Field1 IN (102, 124)

To get the opposite result:
Field1 NOT IN (102, 124)

Let's say that you also want to check for 111 and 222. Instead of a bunch of
OR statements:

Field1 IN (102, 124, 111, 222)

If the data is text, you need quotes:

FirstNameField IN ("Tim", "Jim")
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

The Or worked. Thanks for the additional information on the LIKE operator. I
didn't know that.

Hi All,

[quoted text clipped - 12 lines]

124 it shows the Data for 124. Why doesn't it show the data when I do them
together?

Why are you using LIKE at all? The only function for the LIKE operator is to
use wildcards when searching a variable text string: for instance, Like "M*"
will find all text strings beginning with M. If you don't have a wildcard in
your query, LIKE works the same way as = (although probably it's slower).

As for the AND in your query - AND and OR are English language conjunctions,
but they are also Boolean algebra operators. They look the same, and in some
senses they are synonyms... but they are different.

[Field] = 102 AND [Field] = 124

will evaluate two expressions to see if they are TRUE or FALSE. Let's say the
field contains 102. The first expression will be TRUE; the second will be
FALSE (102 is not equal to 124 after all).

The AND operator returns TRUE if and only if both arguments are TRUE: you have
TRUE AND FALSE

so the result is FALSE (the number in FIELD cannot be equal to 102 and also
equal to 124 at the same time).

If you want the records containing 102 and 124 to both be retreived use OR:
the Boolean operator OR returns TRUE if either or both of its arguments are
TRUE.

[Field] = 102 OR [Field] = 124

will evaluate TRUE OR FALSE - since one of the arguments is true, the whole
expression is true.

John W. Vinson [MVP]

 

[mattc66 via AccessMonster.com]

What if you want to see everything but the 102, and 124?

Since you don't need the wild card, there's an even nicer way to write the
statement.

Field1 IN (102, 124)

To get the opposite result:
Field1 NOT IN (102, 124)

Let's say that you also want to check for 111 and 222. Instead of a bunch of
OR statements:

Field1 IN (102, 124, 111, 222)

If the data is text, you need quotes:

FirstNameField IN ("Tim", "Jim")

The Or worked. Thanks for the additional information on the LIKE operator. I
didn't know that.

[quoted text clipped - 36 lines]

 

[mattc66 via AccessMonster.com]

Disredard I missed it - you already addresed that.. Thanks

What if you want to see everything but the 102, and 124?

Since you don't need the wild card, there's an even nicer way to write the
statement.

[quoted text clipped - 17 lines]