troubleshooting Question

Multicolumn search based on three form fields

Avatar of jagreen78
jagreen78 asked on
Microsoft AccessAdobe DreamweaverSQL
24 Comments1 Solution239 ViewsLast Modified:
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
%>
thecr.zip
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 24 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 24 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros