Avatar of jagreen78
jagreen78 asked on

Multicolumn search based on three form fields

I have two inputs and one drop down box on a form. The user selects a value for all three and then I need to compare each input to different columns in a table.

input: Category
input: City
dropDown: State

Post form: searchresults.asp and do the following:

"Category" to be compared to 5 columns which are:
I could us an "OR" statement here to look through each column and find ONE that matches

Cat1, Cat2, Cat3, Cat4, Cat5

AND

"City" to be compared to 5 columns as well:
City1 & State1 should be checked together and so should the rest. But I only need to find ONE match in the 5 columns.
City1, City2, City3, City4, City5

"State" to be compared as well:
State1, State2, State3, State4, State5

THEN return the records that match this statement on the page.

I have this code below but it only does a search on ONE column PER form field

**I have attached my .mdb file for you to look at the Contractors Table which is what I'm searching through.
'keyword array declarations
  Dim WADbSearch1_KeyArr0, WADbSearch1_KeyArr1
  WADbSearch1_KeyArr0 = Array("Ccity1")
  WADbSearch1_KeyArr1 = Array("Cindustry1")
 
  'comparison list additions
  WADbSearch1_whereClause=BuildKeyword(WADbSearch1_KeyArr0,"" & cStr(Request.Form("City1")) & "","","Includes",",%20","%20","%22","%22",0,WADbSearch1_wildCard,WADbSearch1_dateSeparator,WADbSearch1_whereClause)
  WADbSearch1_whereClause=BuildFromList("Cstate1","Sstate","AND","=",0,WADbSearch1_wildCard,WADbSearch1_dateSeparator,WADbSearch1_whereClause)
  WADbSearch1_whereClause=BuildKeyword(WADbSearch1_KeyArr1,"" & cStr(Request.Form("Scat")) & "","AND","Includes",",%20","%20","%22","%22",0,WADbSearch1_wildCard,WADbSearch1_dateSeparator,WADbSearch1_whereClause)
 
 
  if (1 = 1) then
    Session("WADbSearch1_searchresults")=WADbSearch1_whereClause
  end if
else
  if (1 = 1)     then
    if (cStr(Session("WADbSearch1_searchresults")) <> "")     then
      WADbSearch1_whereClause = cStr(Session("WADbSearch1_searchresults"))
    else
      WADbSearch1_whereClause = WADbSearch1_DefaultWhere
    end if
  else
    WADbSearch1_whereClause = WADbSearch1_DefaultWhere
  end if
end if
%>
<%
Dim searchRS
Dim searchRS_cmd
Dim searchRS_numRows
 
Set searchRS_cmd = Server.CreateObject ("ADODB.Command")
searchRS_cmd.ActiveConnection = MM_theCR_STRING
searchRS_cmd.CommandText = "SELECT * FROM Contractors ORDER BY CoName ASC" 
searchRS_cmd.Prepared = true
 
setQueryBuilderSource searchRS_cmd, WADbSearch1_whereClause, false
Set searchRS = searchRS_cmd.Execute
searchRS_numRows = 0
%>

Open in new window

thecr.zip
Microsoft AccessAdobe DreamweaverSQL

Avatar of undefined
Last Comment
jagreen78

8/22/2022 - Mon
Rouchie

In SQL Server there is a IN predicate for searches.  Don't know if Access supports it but its worth a try.

You basically pass the search query like this...

  SELECT
     *
  FROM
     Contractors
  WHERE
     Category IN (Cat1, Cat2, Cat3) AND
     State IN (State3, State4, State5)
ASKER
jagreen78

This is what I have so far that is looking through the categories correctly,




but I need it to compare city1 AND state1, city2 AND state2, city3 AND state3, etc....as well
Dim askCity
askCity = Request.Form("askCity")
Dim askState
askState = Request.Form("askState")
Dim askCat
askCat = Request.Form("askCat")
 
Dim contractorRS__MMColParam
contractorRS__MMColParam = "y"
If (Request("MM_EmptyValue") <> "") Then 
  contractorRS__MMColParam = Request("MM_EmptyValue")
End If
%>
<%
Dim contractorRS__MMColParam2
contractorRS__MMColParam2 = "y"
If (Request("MM_EmptyValue") <> "") Then 
  contractorRS__MMColParam2 = Request("MM_EmptyValue")
End If
%>
<%
Dim contractorRS__MMCity1
contractorRS__MMCity1 = askCity
If (Request("MM_EmptyValue")   <> "") Then 
  contractorRS__MMCity1 = Request("MM_EmptyValue")  
End If
%>
<%
Dim contractorRS__MMCat
contractorRS__MMCat = askCat
If (Request("MM_EmptyValue")   <> "") Then 
  contractorRS__MMCat = Request("MM_EmptyValue")  
End If
%>
<%
Dim contractorRS__MMCat2
contractorRS__MMCat2 = askCat
If (Request("MM_EmptyValue") <> "") Then 
  contractorRS__MMCat2 = Request("MM_EmptyValue")
End If
%>
<%
Dim contractorRS__MMCat3
contractorRS__MMCat3 = askCat
If (Request("MM_EmptyValue") <> "") Then 
  contractorRS__MMCat3 = Request("MM_EmptyValue")
End If
%>
<%
Dim contractorRS__MMCat4
contractorRS__MMCat4 = askCat
If (Request("MM_EmptyValue") <> "") Then 
  contractorRS__MMCat4 = Request("MM_EmptyValue")
End If
%>
<%
Dim contractorRS__MMCat5
contractorRS__MMCat5 = askCat
If (Request("MM_EmptyValue") <> "") Then 
  contractorRS__MMCat5 = Request("MM_EmptyValue")
End If
%>
<%
Dim contractorRS
Dim contractorRS_cmd
Dim contractorRS_numRows
 
Set contractorRS_cmd = Server.CreateObject ("ADODB.Command")
contractorRS_cmd.ActiveConnection = MM_theCR_STRING
contractorRS_cmd.CommandText = "SELECT * FROM Contractors WHERE CActive = ? AND CQuestions = ? AND Ccity1 = ? OR Cindustry1 = ? OR Cindustry2 = ? OR Cindustry3 = ? OR Cindustry4 = ? OR Cindustry5 = ?" 
contractorRS_cmd.Prepared = true
contractorRS_cmd.Parameters.Append contractorRS_cmd.CreateParameter("param1", 200, 1, 255, contractorRS__MMColParam) ' adVarChar
contractorRS_cmd.Parameters.Append contractorRS_cmd.CreateParameter("param2", 200, 1, 255, contractorRS__MMColParam2) ' adVarChar
contractorRS_cmd.Parameters.Append contractorRS_cmd.CreateParameter("param3", 200, 1, 255, contractorRS__MMCity1) ' adVarChar
contractorRS_cmd.Parameters.Append contractorRS_cmd.CreateParameter("param4", 200, 1, 255, contractorRS__MMCat) ' adVarChar
contractorRS_cmd.Parameters.Append contractorRS_cmd.CreateParameter("param5", 200, 1, 255, contractorRS__MMCat2) ' adVarChar
contractorRS_cmd.Parameters.Append contractorRS_cmd.CreateParameter("param6", 200, 1, 255, contractorRS__MMCat3) ' adVarChar
contractorRS_cmd.Parameters.Append contractorRS_cmd.CreateParameter("param7", 200, 1, 255, contractorRS__MMCat4) ' adVarChar
contractorRS_cmd.Parameters.Append contractorRS_cmd.CreateParameter("param7", 200, 1, 255, contractorRS__MMCat5) ' adVarChar
 
Set contractorRS = contractorRS_cmd.Execute
contractorRS_numRows = 0
%>

Open in new window

Rouchie

What data type is used in the database to hold the 3 column types (ID numbers, text etc?)
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
jagreen78

all are text
Rouchie

From your original post I *think* this is what you need.  Note that DW won't allow you to build this query using the normal methods.   This is too advanced for DW so you have to get stuck in and write it yourself!

The SQL statement is:

SELECT
      *
FROM
      Contractors
WHERE
      ((Cat1 = Category) OR (Cat2 = Category) OR (Cat3 = Category) OR (Cat4 = Category) OR (Cat5 = Category)) AND
      ((City1 = City) OR (City2 = City) OR (City3 = City) OR (City4 = City) OR (City5 = City)) AND
      ((State1 = State) OR (State2 = State) OR (State3 = State) OR (State4 = State) OR (State5 = State))
ORDER BY
      CoName ASC

And translated to ASP becomes:
<%
Dim askCity
Dim askState
Dim askCat
Dim contractorRS
Dim contractorRS_cmd
Dim contractorRS_numRows
 
askCity = Request.Form("askCity")
askState = Request.Form("askState")
askCat = Request.Form("askCat")
 
askCity = Replace(askCity, "'", "''")
askState = Replace(askState, "'", "''")
askCat = Replace(askCat, "'", "''")
 
Set contractorRS_cmd = Server.CreateObject ("ADODB.Command")
contractorRS_cmd.ActiveConnection = MM_theCR_STRING
 
contractorRS_cmd.CommandText = "SELECT	* FROM Contractors WHERE ((Cat1 = '" & askCat & "') OR (Cat2 = '" & askCat & "') OR (Cat3 = '" & askCat & "') OR (Cat4 = '" & askCat & "') OR (Cat5 = '" & askCat & "')) AND ((City1 = '" & askCity & "') OR (City2 = '" & askCity & "') OR (City3 = '" & askCity & "') OR (City4 = '" & askCity & "') OR (City5 = '" & askCity & "')) AND ((State1 = '" & askState & "') OR (State2 = '" & askState & "') OR (State3 = '" & askState & "') OR (State4 = '" & askState & "') OR (State5 = '" & askState & "')) ORDER BY	CoName ASC"
 
Set contractorRS = contractorRS_cmd.Execute
contractorRS_numRows = 0
%>

Open in new window

ASKER
jagreen78

Rouchie you're the man, but tell me one last thing


It is important that City1/State1, City2/State2, City3/State3, etc... be compared together


Is that what you are doing here?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Rouchie

No.  My code works like this....

  GET ALL RECORDS WHERE
      CITY ENTRY MATCHES AT LEAST 1 CITY COLUMN
          AND
      STATE ENTRY MATCHES AT LEAST 1 STATE COLUMN
          AND
      CATEGORY ENTRY MATCHES AT LEAST 1 CATEGORY COLUMN

If that's not right then tell me everything that it needs to find.
ASKER
jagreen78

Going by what you are saying, and you are mostly correct...BUT change this



GET ALL RECORDS WHERE
       CITY AND STATE MATCHES AT LEAST 1 CITY AND STATE
           AND
       CATEGORY ENTRY MATCHES AT LEAST 1 CATEGORY
ASKER
jagreen78

Wouldn't this work?

((City1 = City AND State1=State) OR (City2 = City AND State2=State).......)
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Rouchie

It depends on the entries people are posting.  I don't know what your database holds or what people are entering, so can't really give much more info.

If you can post an example, then I can try to tailor what I did originally.   Are you presuming that people enter the CITY and STATE values as the same thing?

In your post, you cannot double-match things.  You can only match one thing at a time, and use OR/AND to state the importance of each match.
ASKER
jagreen78

Up on my very first post on this message I attached my database (.mdb) file so you could open that up and see the data itself


What else can I provide to help?
Rouchie

I get an error saying the ZIP file is corrupt.

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
jagreen78

just rename the extension to .mdb don't actually try to extract it
Rouchie

Revised SQL statement...

SELECT
      *
FROM
      Contractors
WHERE
      ((Cat1 = '" & askCat & "') OR (Cat2 = '" & askCat & "') OR (Cat3 = '" & askCat & "') OR (Cat4 = '" & askCat & "') OR (Cat5 = '" & askCat & "')) AND
      ((State1 = '" & askState & "' AND City1 = '" & askCity & "') OR (State2 = '" & askState & "' AND City2 = '" & askCity & "') OR (State3 = '" & askState & "' AND City3 = '" & askCity & "') OR (State4 = '" & askState & "' AND City4 = '" & askCity & "') OR (State5 = '" & askState & "' AND City5 = '" & askCity & "'))
ORDER BY
      CoName ASC

In ASP:

contractorRS_cmd.CommandText = "SELECT * FROM Contractors WHERE ((Cat1 = '" & askCat & "') OR (Cat2 = '" & askCat & "') OR (Cat3 = '" & askCat & "') OR (Cat4 = '" & askCat & "') OR (Cat5 = '" & askCat & "')) AND ((State1 = '" & askState & "' AND City1 = '" & askCity & "') OR (State2 = '" & askState & "' AND City2 = '" & askCity & "') OR (State3 = '" & askState & "' AND City3 = '" & askCity & "') OR (State4 = '" & askState & "' AND City4 = '" & askCity & "') OR (State5 = '" & askState & "' AND City5 = '" & askCity & "')) ORDER BY CoName ASC"

Open in new window

ASKER
jagreen78

I tested the code below and get this:

Microsoft JET Database Engine error '80040e10'

No value given for one or more required parameters.

/searchresults.asp, line 24

Line 24 is Set searchRS = searchRS_cmd.Execute
<%
Dim askCity
Dim askState
Dim askCat
Dim searchRS
Dim searchRS_cmd
Dim searchRS_numRows
 
askCity = Request.Form("City1")
askState = Request.Form("Sstate")
askCat = Request.Form("SCat")
 
askCity = Replace(askCity, "'", "''")
askState = Replace(askState, "'", "''")
askCat = Replace(askCat, "'", "''")
 
Set searchRS_cmd = Server.CreateObject ("ADODB.Command")
searchRS_cmd.ActiveConnection = MM_theCR_STRING
 
searchRS_cmd.CommandText = "SELECT * FROM Contractors WHERE ((Cat1 = '" & askCat & "') OR (Cat2 = '" & askCat & "') OR (Cat3 = '" & askCat & "') OR (Cat4 = '" & askCat & "') OR (Cat5 = '" & askCat & "')) AND ((State1 = '" & askState & "' AND City1 = '" & askCity & "') OR (State2 = '" & askState & "' AND City2 = '" & askCity & "') OR (State3 = '" & askState & "' AND City3 = '" & askCity & "') OR (State4 = '" & askState & "' AND City4 = '" & askCity & "') OR (State5 = '" & askState & "' AND City5 = '" & askCity & "')) ORDER BY CoName ASC"
 
Set searchRS = searchRS_cmd.Execute
searchRS_numRows = 0
%>

Open in new window

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
ASKER
jagreen78

Anyone? I'm so close to making this work... :(
ASKER
jagreen78

What about using an "IN" statement?


City & State IN  (City1 & State1, City2 & State2, City3 & State3 etc)
ASKER
jagreen78

What if I had one table with Category 1-5 and another table with the City/States 1-5 and did a JOIN statement??


Would that help?

I tested the SQL statement you have above in my database and it returned zero records
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
jagreen78

YES....................I have a perfectly working SQL JOIN statement but I may need some help converting it to VB Script

SELECT *
FROM Contractors INNER JOIN CS ON Contractors.CID = CS.CID
WHERE (((Contractors.Cindustry1)="Painting Contractors") AND ((CS.Ccity1)="Mount Pleasant") AND ((CS.Cstate1)="SC")) OR (((CS.Ccity1)="Mount Pleasant") AND ((CS.Cstate1)="SC") AND ((Contractors.Cindustry2)="Painting Contractors")) OR (((CS.Ccity1)="Mount Pleasant") AND ((CS.Cstate1)="SC") AND ((Contractors.Cindustry3)="Painting Contractors")) OR (((CS.Ccity1)="Mount Pleasant") AND ((CS.Cstate1)="SC") AND ((Contractors.Cindustry4)="Painting Contractors")) OR (((CS.Ccity1)="Mount Pleasant") AND ((CS.Cstate1)="SC") AND ((Contractors.Cindustry5)="Painting Contractors"));

I need this to fit below the code here: (Do I still need the "Replace" code??)
<%
Dim askCity
Dim askState
Dim askCat
Dim searchRS
Dim searchRS_cmd
Dim searchRS_numRows
 
askCity = Request.Form("City1")
askState = Request.Form("Sstate")
askCat = Request.Form("SCat")
 
Set searchRS_cmd = Server.CreateObject ("ADODB.Command")
searchRS_cmd.ActiveConnection = MM_theCR_STRING

Open in new window

ASKER CERTIFIED SOLUTION
Rouchie

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.
See how we're fighting big data
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
ASKER
jagreen78

I split the Categories and City/State into two tables last night and then made a JOIN statement that is working............now all I have to do is get my dependent drop down working to send the category, city, and state to this form...
ASKER
jagreen78

How do i Change this to VB script?
"SELECT * FROM Contractors INNER JOIN CS ON Contractors.CID = CS.CID WHERE (((Contractors.Cindustry1)='" & askCat & "') AND ((CS.Ccity1)='" & askCity & "') AND ((CS.Cstate1)='" & askState & "')) OR (((CS.Ccity1)='" & askCity & "') AND ((CS.Cstate1)='" & askState & "') AND ((Contractors.Cindustry2)='" & askCat & "')) OR (((CS.Ccity1)='" & askCity & "') AND ((CS.Cstate1)='" & askState & "') AND ((Contractors.Cindustry3)='" & askCat & "')) OR (((CS.Ccity1)='" & askCity & "') AND ((CS.Cstate1)='" & askState & "') AND ((Contractors.Cindustry4)='" & askCat & "')) OR (((CS.Ccity1)='" & askCity & "') AND ((CS.Cstate1)='" & askState & "') AND ((Contractors.Cindustry5)='" & askCat & "')) ORDER BY CoName ASC"

Open in new window

Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
jagreen78

Figured it out...
ASKER
jagreen78

Okay, now I have the correct data that is coming out of my table and displaying


Company - City, State based on the INNER JOIN statement



now when I list this data I want the user to click on the company name to view a Detailspage.asp

I'm trying to do <a href="detailspage.asp?CID=(searchRS("Contractors.CID"))">Company</a>


and on the next page I'm accepting

CID = Request.Querystring("CID")

but it gives me a BOF or EOF error message when I do this


The first code is the search list and the second code is the details page
<%
Dim askCity
Dim askState
Dim askCat
Dim searchRS
Dim searchRS_cmd
Dim searchRS_numRows
 
askCity = Request.Form("City1")
askState = Request.Form("Cstate")
askCat = Request.Form("SCat")
 
askCity = Replace(askCity, "'", "''")
askState = Replace(askState, "'", "''")
askCat = Replace(askCat, "'", "''")
 
Set searchRS_cmd = Server.CreateObject ("ADODB.Command")
searchRS_cmd.ActiveConnection = MM_theCR_STRING
 
searchRS_cmd.CommandText = "SELECT * FROM Contractors INNER JOIN CS ON Contractors.CID = CS.CID WHERE (((Cindustry1)='" & askCat & "') AND ((Ccity1)='" & askCity & "') AND ((Cstate1)='" & askState & "')) OR (((Ccity1)='" & askCity & "') AND ((Cstate1)='" & askState & "') AND ((Cindustry2)='" & askCat & "')) OR (((Ccity1)='" & askCity & "') AND ((Cstate1)='" & askState & "') AND ((Cindustry3)='" & askCat & "')) OR (((Ccity1)='" & askCity & "') AND ((Cstate1)='" & askState & "') AND ((Cindustry4)='" & askCat & "')) OR (((Ccity1)='" & askCity & "') AND ((Cstate1)='" & askState & "') AND ((Cindustry5)='" & askCat & "')) ORDER BY CoName ASC"
 
Set searchRS = searchRS_cmd.Execute
searchRS_numRows = 0
%>
 
 
<%
Dim catRS__MMColParam
catRS__MMColParam = "1"
If (Request.QueryString("CID") <> "") Then 
  catRS__MMColParam = Request.QueryString("CID")
End If
%>
<%
Dim catRS
Dim catRS_cmd
Dim catRS_numRows
 
Set catRS_cmd = Server.CreateObject ("ADODB.Command")
catRS_cmd.ActiveConnection = MM_theCR_STRING
catRS_cmd.CommandText = "SELECT * FROM Contractors WHERE CID = ?" 
catRS_cmd.Prepared = true
catRS_cmd.Parameters.Append catRS_cmd.CreateParameter("param1", 5, 1, -1, catRS__MMColParam) ' adDouble
 
Set catRS = catRS_cmd.Execute
catRS_numRows = 0
%>

Open in new window