Query where field contains "value"

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bob Weaver - 15 Sep 2005 16:30 GMT

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?

Reply to this Message

Brendan Reynolds - 15 Sep 2005 16:37 GMT

In SQL ...

WHERE YourField LIKE "*ad*"

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

LIKE "*ad*"

Signature

Brendan Reynolds (MVP)

>I have a field in a database that contains several different possible
>values:
[quoted text clipped - 4 lines]
> particular fiels contains "ad", regardless of where in the string it may
> appear?

Reply to this Message

Bob Weaver - 15 Sep 2005 16:52 GMT

Brendan,

Thanks! That was quick!

> In SQL ...
>
[quoted text clipped - 12 lines]
> > particular fiels contains "ad", regardless of where in the string it may
> > appear?

Reply to this Message

John Vinson - 15 Sep 2005 17:59 GMT

>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
[quoted text clipped - 3 lines]
>particular fiels contains "ad", regardless of where in the string it may
>appear?

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]

Reply to this Message