We help IT Professionals succeed at work.

where clause

mrong
mrong used Ask the Experts™
on
Greeting,
I have a select stmt in access like the following. if I use 'in' it gives me all the records as long as it match one specialty in the string. Is there anyway to make it match all the specialty in STRING. I know I can use '=', but my problem is my STRING is so long and I really don't want split it.

select tbl1.* from tbl1 where tbl1.specialty in (STRING);

below is some sample data for tbl1

ID         specialty
001      roofing
001      plumbling
002      Interior design
002      roofing
002      parking lot
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
where instr(myString,tbl1.specialty)

noticed thast i changer Sting to myString, since it's not good coding practice to use a type as the name of a variable

Commented:
sorry
where instr(myString,tbl1.specialty) <> 0

Author

Commented:
When I tested your suggestion using the sql below in access query I got syntax error.
 
select tbl1.* from tbl1 where instr(tbl1.specialty,('Roofing','Parking Lot'));

thanks.

Commented:
select tbl1.* from tbl1 where instr(tbl1.specialty,'Roofing') <> 0 OR instr(tbl1.specialty,'Parking Lot') <> 0

Commented:
this would bring up all records where "roofing" or "parking lot" is anywhere in the field specialty...

Author

Commented:
Surone1:
Is there anyway I can keep ('Roofing','Parking Lot')? I don't want to change my asp to split the string.
thanks

Author

Commented:
I tried using WHERE instr(Specialty_Master.Specialty_details,('Master Plan','Roofing'))<>0 but it gave me syntax error.

thanks.

Commented:
you could try instr('Roofing Parking Lot',tbl1.specialty) <> 0

Author

Commented:
same error
Commented:
that's strange..
if i try
msgbox instr("Roofing Parking Lot","roofing") <> 0
in an immediate window it seems to work..
can you paste the entire line for me as you have it?

Author

Commented:
SELECT tbl1.*
FROM tbl1 LEFT JOIN tbl2 ON tbl1.FirmID=tbl2.FirmID
WHERE instr(('Master Plan','Roofing'),tbl2.Specialty_details)<>0;

I tried above in access query
thanks.

Author

Commented:
I tried the following in access and it gave me the records which matched either 'Master Plan' OR 'Roofing'). Can it only return me the records match BOTH? Thanks.

SELECT tbl1.*
FROM tbl1
WHERE instr(('Master Plan','Roofing'),tbl1.Specialty_details)<>0;

Author

Commented:
I will request to close this question if no one can provide a solution. thanks.
Top Expert 2004
Commented:
Why can't you just do:
SELECT * FROM tbl1 WHERE tbl1.Specialty_details IN('Master Plan','Roofing')

?

When you say records that match both, how can the field Specialty_details contain both Master Plan and Roofing in the same record? Looking at your sample data, this doesn't seem possible.

Author

Commented:
Below is my table structure.
ID         specialty
---        --------------
001      roofing
001      plumbling
002      Interior design
002      roofing
002      parking lot
Top Expert 2004

Commented:
Yes, so one record can only ever have one specialty in it. The code that has been given will record all records that have either "roofing" or "master plan" in the specialty field. Isn't that what you want?

Author

Commented:
Yes, one record can only have one speciaty. But each ID can have many specialties. LIke ID->002 has Interior design,roofing,parking lot. Using 'IN" won't work coz I need to make sure all 3 specialty matched, not only 1. thanks.