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
Microsoft Access

Avatar of undefined
Last Comment
mrong

8/22/2022 - Mon
Surone1

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

Surone1

sorry
where instr(myString,tbl1.specialty) <> 0
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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Surone1

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

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

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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
mrong

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

thanks.
Surone1

you could try instr('Roofing Parking Lot',tbl1.specialty) <> 0
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
mrong

ASKER
same error
ASKER CERTIFIED SOLUTION
Surone1

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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.
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;
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
mrong

ASKER
I will request to close this question if no one can provide a solution. thanks.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
mrong

ASKER
Below is my table structure.
ID         specialty
---        --------------
001      roofing
001      plumbling
002      Interior design
002      roofing
002      parking lot
shanesuebsahakarn

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?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
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.