FP SQL using "in"

Hello,
I have the following query to pull data in FP2000: s-sql="SELECT * FROM Company WHERE ('::CompanyName::' in (",CompanyName)) AND ('::City::' in (",City)) AND ('::County::' in (",County)) AND ('::StateorProvince::' in (",StateorProvince)) AND ('::Postalcode::' in (",Postalcode)) ORDER BY CompanyName ASC

When I save it drops the second part of the first fields values:  WHERE ('::CompanyName::' in (''AND ... the rest of the code is fine.  

I have five text boxes and the user does not have to fill all of them in to run the query.  It should run using as many of the fields as they populate.

Will this code work with some kind of modification for the "in" statement?  If not, how do I go about this query?

Thank you.
tanapdAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tanapdAuthor Commented:
Adjusted points to 150
0
NickRackhamCommented:
How about

s-sql="SELECT * FROM Company WHERE CompanyName='" & Request.Form("CompanyName") & "', AND City='" & Request.Form("City") & "' AND Country='" & Request.Form("Country") & "' AND StateorProvince='" & Request.Form("StateorProvince") & "' AND Postalcode='" & Request.Form("Postalcode") & "' ORDER BY CompanyName ASC

Nick

0
tanapdAuthor Commented:
Hi, This won't work because all passed parameters would require a value.  Using the "in" would allow the value of any or all of the parameters to be a blank value or a passed value.  Any other suggestions?
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

NickRackhamCommented:
I don't see why it won't work for you!! This works for me in a number of situations. Have you tried it? If not please do and then tell me it doesn't work.

Here is a sample of code I currently use.

"SELECT PhoneBook.FirstName, PhoneBook.LastName, PhoneBook.Department, PhoneBook.Section, PhoneBook.JobTitle, PhoneBook.Location, PhoneBook.Extension, PhoneBook.DirectDial  FROM PhoneBook  WHERE (((PhoneBook.FirstName) Like '" & Request.QueryString("[FirstName]") & "%%') AND ((PhoneBook.LastName) Like '" & Request.QueryString("[LastName]") & "%%') AND ((PhoneBook.Department) Like '" & Request.QueryString("[Department]") & "%%') AND ((PhoneBook.Section) Like '" & Request.QueryString("[Section]") & "%%') AND ((PhoneBook.Location) Like '" & Request.QueryString("[Location]") & "%%') AND ((PhoneBook.Extension) Like '" & Request.QueryString("[Extension]") & "%%'))ORDER BY [LastName]   "

Try it and see
0
tanapdAuthor Commented:
Hi,

The sample code above that you use uses "Like" with wild characters, not "=".  That would make a difference.  I will try that.  Can you please post your response as an answer so if it works I can give you the points?

Thanks!
0
NickRackhamCommented:
No Problem,

Posted as an answer as requested.

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tanapdAuthor Commented:
Thank you!
0
NickRackhamCommented:
No Problem, Enjoy :o)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Development Software

From novice to tech pro — start learning today.