Need something like a Contains Clause

V

Vic Spainhower

Hello,

I'm trying to create a select statement and if Access supported a contains
clause would probably solve the problem. I need to pull all of the records
from the table which contain a specific value i.e SELECT * FROM qryTest
WHERE AllowedDivisions Contains 'Z' would only pull the first 3 and last 2.
I'm stuck can't figure out how to do it. Any ideas? I'm using Access 2002.


qryTest ClassDsc CLASSID ShowNbr AllowedDivisions
TWH Western Pleasure Open 3-gait 060 1 'Z'
TWH Western Pleasure Open 3-gait championship 090 1 'Z'
TWH Working Trail Pleasure E/W 2-gt with Obstacles 038 1 'Z'
TWH/ RH In-Hand Halter Stallions 2 & Over 003 1 'R','T'
TWH/RH In-Hand Halter Mares & Geldings 2 & Over 004 1 'T','S','U'
TWH/RH In-hand Halter Weanlings & Yearlings 002 1 'Z'
TWHWorking Trail Pleasure E/W 3-gt with Obstacles 043 1 'Z'



Thanks,

Vic
 
N

Norman Yuan

If you do not need to exclude the situation where more than one 'Z' in the
same record or 'Z' and other ('T', or 'U'...), you can just use a LIKE key
word:

SELECT...WHERE AllowedDivisions LIKE '*''Z''*'
 
D

Dirk Goldgar

Vic Spainhower said:
Hello,

I'm trying to create a select statement and if Access supported a
contains clause would probably solve the problem. I need to pull all
of the records from the table which contain a specific value i.e
SELECT * FROM qryTest WHERE AllowedDivisions Contains 'Z' would only
pull the first 3 and last 2. I'm stuck can't figure out how to do it.
Any ideas? I'm using Access 2002.


qryTest ClassDsc CLASSID ShowNbr AllowedDivisions
TWH Western Pleasure Open 3-gait 060 1 'Z'
TWH Western Pleasure Open 3-gait championship 090 1 'Z'
TWH Working Trail Pleasure E/W 2-gt with Obstacles 038 1 'Z'
TWH/ RH In-Hand Halter Stallions 2 & Over 003 1 'R','T'
TWH/RH In-Hand Halter Mares & Geldings 2 & Over 004 1
'T','S','U' TWH/RH In-hand Halter Weanlings & Yearlings 002 1
'Z' TWHWorking Trail Pleasure E/W 3-gt with Obstacles 043 1 'Z'
Click to expand...

The problem is caused by a faulty table design, in which one field,
AllowedDivisions, contains multiple data values. Those should really be
separate records in a related table.

However, dealing with the situation as it stands, you can use the
InStr() function inefficiently but effectively in your query's WHERE
clause:

SELECT * FROM qryTest
WHERE InStr(AllowedDivisions, "'Z'") <> 0
 
L

Lynn Trapp

V

Vic Spainhower

Thanks for the great replys ...

The problem with the like is the AllowedDivisions may contain 'Z1' and they
would also show up. I'm trying to use the instr function as Dirk suggested
and searching for the value and including the quote marks. I think this
should work.

Lynn, you're right it probably should be a separate database but this is a
patch to an existing application and I thought doing it this way would have
less impact..


Thanks Again,

Vic
 
D

Dirk Goldgar

Vic Spainhower said:
Thanks for the great replys ...

The problem with the like is the AllowedDivisions may contain 'Z1'
and they would also show up. I'm trying to use the instr function as
Dirk suggested and searching for the value and including the quote
marks. I think this should work.

Lynn, you're right it probably should be a separate database but this
is a patch to an existing application and I thought doing it this way
would have less impact..
Click to expand...

Note that the Like expressions people have posted do include the
single-quotes, so they should work to pull 'Z' and not 'Z1'. I actually
prefer those to using the InStr() method; I just didn't think of the
Like operator.
 
V

Vic Spainhower

Dirk,

I tried ((ClassMaster.AllowedDivisions) Like "*" & 'Z' & "*")) and it
pulled Z1 but ((ClassMaster.AllowedDivisions) Like "*" & "'Z'" & "*"))
works. Thanks for the tip I'll use LIKE

Vic
 
D

Dirk Goldgar

Vic Spainhower said:
Dirk,

I tried ((ClassMaster.AllowedDivisions) Like "*" & 'Z' & "*")) and
it pulled Z1 but ((ClassMaster.AllowedDivisions) Like "*" & "'Z'" &
"*")) works. Thanks for the tip I'll use LIKE
Click to expand...

Oh, you're right, Lynn's did leave off the double-quotes around 'Z'.
Glad you got it sorted out.
 
Top