Link to home
Create AccountLog in
Avatar of mrong
mrong

asked on

where clause

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
Avatar of Surone1
Surone1
Flag of Suriname image

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

sorry
where instr(myString,tbl1.specialty) <> 0
Avatar of mrong
mrong

ASKER

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.
select tbl1.* from tbl1 where instr(tbl1.specialty,'Roofing') <> 0 OR instr(tbl1.specialty,'Parking Lot') <> 0
this would bring up all records where "roofing" or "parking lot" is anywhere in the field specialty...
Avatar of mrong

ASKER

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

ASKER

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

thanks.
you could try instr('Roofing Parking Lot',tbl1.specialty) <> 0
Avatar of mrong

ASKER

same error
ASKER CERTIFIED SOLUTION
Avatar of Surone1
Surone1
Flag of Suriname image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of mrong

ASKER

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.
Avatar of mrong

ASKER

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;
Avatar of mrong

ASKER

I will request to close this question if no one can provide a solution. thanks.
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of mrong

ASKER

Below is my table structure.
ID         specialty
---        --------------
001      roofing
001      plumbling
002      Interior design
002      roofing
002      parking lot
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?
Avatar of mrong

ASKER

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.