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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
NickRackhamConnect With a Mentor Commented:
No Problem,

Posted as an answer as requested.

0
 
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
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
 
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
 
tanapdAuthor Commented:
Thank you!
0
 
NickRackhamCommented:
No Problem, Enjoy :o)
0
All Courses

From novice to tech pro — start learning today.