Query where field contains "value"

B

Bob Weaver

I have a field in a database that contains several different possible values:
ad, am, rp, etc. It is possible to have a combination of values, such as
ad/rp, etc.

Can I use a criteria selection to select only those records where the
particular fiels contains "ad", regardless of where in the string it may
appear?
 
B

Brendan Reynolds

In SQL ...

WHERE YourField LIKE "*ad*"

In query design view, in the Criteria row, that would look like ...

LIKE "*ad*"
 
B

Bob Weaver

Brendan,

Thanks! That was quick!

Brendan Reynolds said:
In SQL ...

WHERE YourField LIKE "*ad*"

In query design view, in the Criteria row, that would look like ...

LIKE "*ad*"
Click to expand...
 
J

John Vinson

I have a field in a database that contains several different possible values:
ad, am, rp, etc. It is possible to have a combination of values, such as
ad/rp, etc.

Can I use a criteria selection to select only those records where the
particular fiels contains "ad", regardless of where in the string it may
appear?
Click to expand...

Brendan's suggestion will, of course, work fine... BUT you may be
causing yourself a problem.

If a field can take on multiple values, you probably have a many to
many relationship. Single fields should contain only a single value!

Consider splitting this out into two additional tables: a table of
codes (don't know what you'ld call it since I don't know what these
values mean) with values ad, am, rp, and so on - just a small table
with perhaps only one field (or two, if you want to add a
human-readable translation); and a "resolver" table with fields for
the primary key of your main table, and the code.

If an item has two codes, this resolver table would have TWO records,
one for each. You could use a Subform to enter and display the data.


John W. Vinson[MVP]
 
Top