?
Solved

Display results from multiple search criteria

Posted on 2011-10-18
8
Medium Priority
?
214 Views
Last Modified: 2012-05-12
I am using
- MSAccess
- Microsoft SQL Server  2000 - 8.00.760 (Intel X86)
- FrontPage 2003

I am at a loss here. I have a search form with 7 fields. I need to allow the user to be able to fill-in any field or combination of fields to search for the result. All fields work individually with the current code. Code does NOT search correctly when there is more than one data field used, it will only display the results for the first field where there is matching data.

The fields I want to be able to use as search criteria are:
- FName
- LName
- Position
- Facility
- City
- State

I am new to this type of code...

I am attaching two sets of code. The default.asp page and the results.asp page. I know I am missing the joining when there is more than one search criteria, but I do not know where to start.


**************************************************
default.asp CODE
**************************************************
<FORM METHOD="POST" ACTION="Results.asp">

<!-- Following is search option for NAME -->
<b>Name</b> (First or Last): 
<input type="TEXT" NAME="Keyword" SIZE="25">


<!-- Following is the drop-down Position selection option -->
<b>Position</b>:
		
	<%
		Dim objRS, position, sql

		Response.Write ("<select name = 'Position'>")
		Response.Write ("<option value=''>-- All --</option>")
			Set objRS = Server.CreateObject("ADODB.Recordset")


		sql = "SELECT DISTINCT LibManTitle FROM tblStaffDirectory "

				If Request.querystring("sort") = "" then
					sql = sql & "ORDER BY LibManTitle"
				Else
		  			sql = sql & "ORDER BY " & Request.querystring("sort")
		  		End If			
	
				objRS.Open sql, objConn

		While Not objRS.EOF
			position = objRS("LibManTitle")
			Response.Write "<option value = '" & position & "'>"
			Response.Write position & "</option>"
			objRS.MoveNext
		Wend
		Response.Write ("</select>")
		objRS.Close

	%>


<!-- Following is the drop-down Facility Name selection option -->
<p><b>Facility Name</b>:
		
		<%
		Dim facility

		Response.Write ("<select name = 'facilityname'>")
		Response.Write ("<option value=''>-- All --</option>")
			Set objRS = Server.CreateObject("ADODB.Recordset")


		sql = "SELECT DISTINCT FacName FROM tblGeneral " & _
			  "UNION " & _
			  "SELECT DISTINCT CorpName FROM tblGeneral "

				If Request.querystring("sort") = "" then
					sql = sql & "ORDER BY FacName"
				Else
		  			sql = sql & "ORDER BY " & Request.querystring("sort")
		  		End If			
	
			objRS.Open sql, objConn

		While Not objRS.EOF
			facility = objRS("FacName")
			Response.Write "<option value = '" & facility & "'>"
			Response.Write facility & "</option>"
			objRS.MoveNext
		Wend
		Response.Write ("</select>")
		objRS.Close
		%>


<!-- Following is the drop-down City selection option -->
<p><b>City</b>:
		
		<%
		Dim city

		Response.Write ("<select name = 'city'>")
		Response.Write ("<option value=''>-- All --</option>")
			Set objRS = Server.CreateObject("ADODB.Recordset")


		sql = "SELECT DISTINCT City FROM tblGeneral "

				If Request.querystring("sort") = "" then
					sql = sql & "ORDER BY City"
				Else
		  			sql = sql & "ORDER BY " & Request.querystring("sort")
		  		End If			
				
			objRS.Open sql, objConn

		While Not objRS.EOF
			city = objRS("City")
			Response.Write "<option value = '" & city & "'>"
			Response.Write city & "</option>"
			objRS.MoveNext
		Wend
		Response.Write ("</select>")
		objRS.Close
		%>
	
	
<!-- Following is the drop-down State selection option -->
<b>State</b>:
		
		<%
		Dim state

		Response.Write ("<select name = 'state'>")
		Response.Write ("<option value=''>-- All --</option>")
			Set objRS = Server.CreateObject("ADODB.Recordset")


		sql = "SELECT DISTINCT State FROM tblGeneral "

				If Request.querystring("sort") = "" then
					sql = sql & "ORDER BY State"
				Else
		  			sql = sql & "ORDER BY " & Request.querystring("sort")
		  		End If			
	
			
			objRS.Open sql, objConn

		While Not objRS.EOF
			state = objRS("State")
			Response.Write "<option value = '" & state & "'>"
			Response.Write state & "</option>"
			objRS.MoveNext
		Wend
		Response.Write ("</select>")
		objRS.Close
		%>
	

<!-- Following is search option for VISN -->		
<b>VISN:</b>


<select name="visn_list" size="1">
		<option value="" selected>-- All --</option>
		<option value="1" >VISN 1</option>
		<option value="2" >VISN 2</option>
		<option value="3" >VISN 3</option>
		<option value="4" >VISN 4</option>
		<option value="5" >VISN 5</option>
		<option value="6" >VISN 6</option>
		<option value="7" >VISN 7</option>
		<option value="8" >VISN 8</option>
		<option value="9" >VISN 9</option>
		<option value="10" >VISN 10</option>
		<option value="11" >VISN 11</option>
		<option value="12" >VISN 12</option>
		<option value="13" >VISN 13</option>
		<option value="14" >VISN 14</option>
		<option value="15" >VISN 15</option>
		<option value="16" >VISN 16</option>
		<option value="17" >VISN 17</option>
		<option value="18" >VISN 18</option>
		<option value="19" >VISN 19</option>
		<option value="20" >VISN 20</option>
		<option value="21" >VISN 21</option>
		<option value="22" >VISN 22</option>
		<option value="23" >VISN 23</option>
		</select>


<!-- Following is the SORT option -->		
<p><b>Sort By:</b>
	
	<select name="sort_by" size="1">
		<option value="" selected>Last name</option>
		<option value="City" >City</option>
		<option value="State" >State</option>
	</select>




<p>
	<input type="SUBMIT" Value="Search">
	<input type="reset" value="Clear">



</FORM>

**************************************************
results.asp CODE
**************************************************
<%

Dim objRS, StrKeyword, StrVISN, sql
	StrKeyword = request.form("keyword")
	StrPosition = request.form("position")
	StrFacility = request.form("facilityname")
	StrCity = request.form("city")
	StrState = request.form("state")	
	StrVISN = request.form("visn_list")
	
 sql = "SELECT tblStaffDirectory.FName, tblStaffDirectory.LName, tblStaffDirectory.LibManTitle, tblStaffDirectory.Phone, tblStaffDirectory.Ext, tblStaffDirectory.Email, tblGeneral.StreetAddr, tblGeneral.City, tblGeneral.State, tblGeneral.Zipcode, tblGeneral.VISNID, tblGeneral.FacName, tblGeneral.CorpName " & _
	"FROM tblStaffDirectory LEFT JOIN tblGeneral ON tblStaffDirectory.ChartID= tblGeneral.ChartID "
	  	
            If StrKeyword <> "" then
                   StrKeyword=Replace(StrKeyword,"'","''")
	               Sql = sql & "WHERE FName LIKE '" & StrKeyword & "%' OR LName LIKE '" & StrKeyword & "%'"
                     
            ElseIf StrPosition <> "" then
                   StrPosition=Replace(StrPosition,"'","''")
                   Sql = sql & "WHERE LibManTitle LIKE '" & StrPosition & "'"                       
                        
            ElseIf StrFacility <> "" then
                   StrFacility=Replace(StrFacility,"'","''")
                   Sql = sql & "WHERE FacName LIKE '" & StrFacility & "' OR CorpName LIKE '" & StrFacility & "'"                       
            
            ElseIf StrCity <> "" then
                   StrCity=Replace(StrCity,"'","''")
                   Sql = sql & "WHERE City LIKE '" & StrCity & "'"                       
                    
            ElseIf StrState <> "" then
                   StrState=Replace(StrState,"'","''")
                   Sql = sql & "WHERE State LIKE '" & StrState & "'"                       
            
            ElseIf StrVISN <> "" then
                   StrVISN=Replace(StrVISN,"'","''")
                   Sql = sql & "WHERE VISNID LIKE '" & StrVISN & "'"
             
			End If			
            
            If Request.form("sort_by") = "" then
		 			sql = sql & "ORDER BY LName"
			Else
 					sql = sql & "ORDER BY " & Request.form("sort_by")
 			End If			

'Diagnostic for sql statement errors
'response.write "SQL statement:<font color='red'>" &  sql & "</font><p>"
	
	Set objRS = Server.CreateObject("ADODB.Recordset")
	objRS.Open sql, objConn
	
'Determine if there are results
	If objRS.EOF then
		'If no records
		Response.Write "There are no individuals listed for the search criteria requested."
	Else


Response.Write "<TABLE width='60%' border='0' cellpadding='0' cellspacing='5' class='CCTable'>"

While Not objRS.EOF


Response.Write "<TR>"

If Len(objRS("FName"))>2 then
	Response.Write "<b>" & objRS("FName") & " " & objRS("LName") & "</b>"
	
	If Len(objRS("LibManTitle"))>2 then
		Response.Write ", " & objRS("LibManTitle")
	End If

	Response.Write "<br>"

	If Len(objRS("CorpName"))>2 then
		Response.Write objRS("CorpName") & "<br>"
	End If
	
	If Len(objRS("FacName"))>2 then
		Response.Write objRS("FacName") & ", "
	End If
	
	Response.Write "VISN " & objRS("VISNID") & "<br>"

	If Len(objRS("StreetAddr"))>2 then
		Response.Write objRS("StreetAddr") & "<br>"
	End If

	If Len(objRS("City"))>2 then
		Response.Write objRS("City") & ", " & objRS("State") & " " & objRS("Zipcode") & "<br>"
	End If

	If Len(objRS("Phone"))>2 then
		Response.Write "Phone: " & objRS("Phone")
		
		If Len(objRS("Ext"))>2 then
			Response.Write "  x" & objRS("Ext") 
		End If

		Response.Write "<br>"
	
	End If

	If Len(objRS("Email"))>2 then
		Response.Write ("<a href=""mailto:" & objRS("Email") & """>" & objRS("Email") & "</a>") & "<br>"
	End If
	
	End If

   'Advance the recordset
   objRS.MoveNext
   
Response.Write "<br>"
Wend
End If
objRS.Close
Set objRS = Nothing

Response.Write "</TABLE>" & VbCrLf
%>

Open in new window

0
Comment
Question by:JLohman
  • 3
  • 3
  • 2
8 Comments
 
LVL 14

Expert Comment

by:pteranodon72
ID: 36989173
I can't debug the the whole ASP, but I can give you some strategy for SQL WHERE clauses:

-- First, you use LIKE in each comparison, but you only have wildcards (%) in some.

LName Like 'abc'

gives the same results as

LName = 'abc'

Do you want
LName = '%abc%'
?

It seems to me that most of these searches can use = because the exact database matches are used. I'd only use Like if the user can enter comparison text that is not a match to a field value -- intentionally enter 'Jo' to match 'John' or 'Jonathan'. I wrote the code using LIKE %abc%. You can change some or all of them to = and remove %s for faster code with fewer matches.

-- Second, WHERE can only appear once in a query. There may be multiple conditions, but only one WHERE. Use a separate temp string to build up the criteria. I put each clause into the strWhere variable starting with an AND and ending with a space. After they are all combined, I strip off the first AND.

-- Third, use separate If-Then-End If, not ElseIf so that multiple criteria (not just the first match) are applied.

At line 211-241 try:

Dim strWhere
If StrKeyword <> "" then
    StrKeyword=Replace(StrKeyword,"'","''")
    strWhere = strWhere & "AND (FName LIKE '%" & StrKeyword & "%' OR LName LIKE '%" & StrKeyword & "%') "
End If

If StrPosition <> "" then
    StrPosition=Replace(StrPosition,"'","''")
    strWhere = strWhere & "AND LibManTitle LIKE '%" & StrPosition & "%' "
End If

If StrFacility <> "" then
    StrFacility=Replace(StrFacility,"'","''")
   strWhere = strWhere & "AND (FacName LIKE '%" & StrFacility & "%' OR CorpName LIKE '%" & StrFacility & "%') "                       
End If

If StrCity <> "" then
   StrCity=Replace(StrCity,"'","''")
   strWhere = strWhere & "WHERE City LIKE '" & StrCity & "' "                       
End If

If StrState <> "" then
   StrState=Replace(StrState,"'","''")
   strWhere = strWhere  & "AND State LIKE '" & StrState & "' "                       
End If
            
If StrVISN <> "" then
   StrVISN=Replace(StrVISN,"'","''")
   strWhere = strWhere & "AND VISNID LIKE '" & StrVISN & "' "
End If			

'If one or more conditions were specified, strip off the initial "AND "
If Len(strWhere) > 0 then
   strWhere = Right(strWhere, Len(strWhere) - 4)
End If
            
If Request.form("sort_by") = "" then
   sql = sql & strWhere & "ORDER BY LName"
Else
   sql = sql & strWhere & "ORDER BY " & Request.form("sort_by")
End If			

Open in new window


HTH,

pT72
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36989196
Starting at line 208 in your code, try modifying your SQL as follows:

 sql = "SELECT tblStaffDirectory.FName, tblStaffDirectory.LName, tblStaffDirectory.LibManTitle, tblStaffDirectory.Phone, tblStaffDirectory.Ext, tblStaffDirectory.Email, tblGeneral.StreetAddr, tblGeneral.City, tblGeneral.State, tblGeneral.Zipcode, tblGeneral.VISNID, tblGeneral.FacName, tblGeneral.CorpName " & _
	"FROM tblStaffDirectory LEFT JOIN tblGeneral ON tblStaffDirectory.ChartID= tblGeneral.ChartID "
          
            sql = sql & "WHERE 1 "
	  	
            If StrKeyword <> "" then
                   StrKeyword=Replace(StrKeyword,"'","''")
	               Sql = sql & "AND (FName LIKE '" & StrKeyword & "%' OR LName LIKE '" & StrKeyword & "%') "
                     
            ElseIf StrPosition <> "" then
                   StrPosition=Replace(StrPosition,"'","''")
                   Sql = sql & "AND LibManTitle LIKE '" & StrPosition & "' "                       
                        
            ElseIf StrFacility <> "" then
                   StrFacility=Replace(StrFacility,"'","''")
                   Sql = sql & "AND  FacName LIKE '" & StrFacility & "' OR CorpName LIKE '" & StrFacility & "' "                       
            
            ElseIf StrCity <> "" then
                   StrCity=Replace(StrCity,"'","''")
                   Sql = sql & "AND City LIKE '" & StrCity & "' "                       
                    
            ElseIf StrState <> "" then
                   StrState=Replace(StrState,"'","''")
                   Sql = sql & "AND State LIKE '" & StrState & "' "                       
            
            ElseIf StrVISN <> "" then
                   StrVISN=Replace(StrVISN,"'","''")
                   Sql = sql & "AND VISNID LIKE '" & StrVISN & "' "
             
			End If			
            
            If Request.form("sort_by") = "" then
		 			sql = sql & "ORDER BY LName"
			Else
 					sql = sql & "ORDER BY " & Request.form("sort_by")
 			End If			

Open in new window

0
 

Author Comment

by:JLohman
ID: 36990265
pteranodon72: I tried your code and get the following error:

SQL statement:SELECT tblStaffDirectory.FName, tblStaffDirectory.LName, tblStaffDirectory.LibManTitle, tblStaffDirectory.Phone, tblStaffDirectory.Ext, tblStaffDirectory.Email, tblGeneral.StreetAddr, tblGeneral.City, tblGeneral.State, tblGeneral.Zipcode, tblGeneral.VISNID, tblGeneral.FacName, tblGeneral.CorpName FROM tblStaffDirectory LEFT JOIN tblGeneral ON tblStaffDirectory.ChartID= tblGeneral.ChartID E City LIKE 'Brooklyn' AND State LIKE 'NY' ORDER BY LName

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'tblStaffDirectory.ChartID= tblGeneral.ChartID E City LIKE 'Brooklyn''.

Results.asp, line 127 [which is:   objRS.Open sql, objConn]

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:JLohman
ID: 36990268
mbizup:  your suggested code is only using the first data entered. For example is you enter a position title and state, it is only displaying the position titles. It is not finding the records matching both criteria.

0
 
LVL 61

Expert Comment

by:mbizup
ID: 36991775
<your suggested code is only using the first data entered>

I missed it in my post last night, but pteranodon72 has the reason in the first post.  You need to seperate these criteria in to distinct If-Then blocks.

The ELSE statements effectively make the code progression escape any furhter steps, so if one condition is met, none of the others are checked.

If you seperate them in to distinct If-Then blocks as suggested by pteranodon72, each condition is checked regardless of whether the previos condition was met or not (this is what you want).
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 800 total points
ID: 36991798
Intgrating that in to my post:

 sql = "SELECT tblStaffDirectory.FName, tblStaffDirectory.LName, tblStaffDirectory.LibManTitle, tblStaffDirectory.Phone, tblStaffDirectory.Ext, tblStaffDirectory.Email, tblGeneral.StreetAddr, tblGeneral.City, tblGeneral.State, tblGeneral.Zipcode, tblGeneral.VISNID, tblGeneral.FacName, tblGeneral.CorpName " & _
	"FROM tblStaffDirectory LEFT JOIN tblGeneral ON tblStaffDirectory.ChartID= tblGeneral.ChartID "
          
            sql = sql & "WHERE 1 "
	  	
            If StrKeyword <> "" then
                   StrKeyword=Replace(StrKeyword,"'","''")
	               Sql = sql & "AND (FName LIKE '" & StrKeyword & "%' OR LName LIKE '" & StrKeyword & "%') "
             End If        
            If StrPosition <> "" then
                   StrPosition=Replace(StrPosition,"'","''")
                   Sql = sql & "AND LibManTitle LIKE '" & StrPosition & "' "                       
            End IF            
            If StrFacility <> "" then
                   StrFacility=Replace(StrFacility,"'","''")
                   Sql = sql & "AND  FacName LIKE '" & StrFacility & "' OR CorpName LIKE '" & StrFacility & "' "                       
            End IF
            If StrCity <> "" then
                   StrCity=Replace(StrCity,"'","''")
                   Sql = sql & "AND City LIKE '" & StrCity & "' "                       
            End IF       
            If StrState <> "" then
                   StrState=Replace(StrState,"'","''")
                   Sql = sql & "AND State LIKE '" & StrState & "' "                       
            End IF
            If StrVISN <> "" then
                   StrVISN=Replace(StrVISN,"'","''")
                   Sql = sql & "AND VISNID LIKE '" & StrVISN & "' "
             
            End If			
            
            If Request.form("sort_by") = "" then
		 			sql = sql & "ORDER BY LName"
			Else
 					sql = sql & "ORDER BY " & Request.form("sort_by")
 			End If			 

Open in new window

0
 
LVL 14

Accepted Solution

by:
pteranodon72 earned 1200 total points
ID: 36992300
Sorry about that -- that, I left in one where instead of an AND in the City component. I also changed line 34 below to add a single WHERE to the clause.
pT72

Dim strWhere
If StrKeyword <> "" then
    StrKeyword=Replace(StrKeyword,"'","''")
    strWhere = strWhere & "AND (FName LIKE '%" & StrKeyword & "%' OR LName LIKE '%" & StrKeyword & "%') "
End If

If StrPosition <> "" then
    StrPosition=Replace(StrPosition,"'","''")
    strWhere = strWhere & "AND LibManTitle LIKE '%" & StrPosition & "%' "
End If

If StrFacility <> "" then
    StrFacility=Replace(StrFacility,"'","''")
   strWhere = strWhere & "AND (FacName LIKE '%" & StrFacility & "%' OR CorpName LIKE '%" & StrFacility & "%') "                       
End If

If StrCity <> "" then
   StrCity=Replace(StrCity,"'","''")
   strWhere = strWhere & "AND City LIKE '" & StrCity & "' "                       
End If

If StrState <> "" then
   StrState=Replace(StrState,"'","''")
   strWhere = strWhere  & "AND State LIKE '" & StrState & "' "                       
End If
            
If StrVISN <> "" then
   StrVISN=Replace(StrVISN,"'","''")
   strWhere = strWhere & "AND VISNID LIKE '" & StrVISN & "' "
End If			

'If one or more conditions were specified, strip off the initial "AND "
If Len(strWhere) > 0 then
   strWhere = "WHERE " & Right(strWhere, Len(strWhere) - 4)
End If
            
If Request.form("sort_by") = "" then
   sql = sql & strWhere & "ORDER BY LName"
Else
   sql = sql & strWhere & "ORDER BY " & Request.form("sort_by")
End If			

Open in new window

0
 

Author Closing Comment

by:JLohman
ID: 36993401
Thanks guys. Perfect, easy to understand solution. I hope you ALWAYS answer my questions.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

750 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question