[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

No results on search page after keywords entered

Posted on 2013-01-10
29
Medium Priority
?
401 Views
Last Modified: 2013-01-10
I am trying to search a memo field in an Access database (proc_info), using up to three keywords. A small section of the search section is below:

<td width="210" valign="middle"><strong><font color="#666666">Search 
                      with up to three keywords</font></strong></td>
                    <td width="115"><input name="search1" type="text" class="text-medium" id="search1" size="15" value="<%= strSearch1 %>"></td>
                    <td width="115"><input name="search2" type="text" class="text-medium" id="search2" size="15" value="<%= strSearch2 %>"></td>
                    <td width="120"><input name="search3" type="text" class="text-medium" id="search3" size="15" value="<%= strSearch3 %>">
                    </td>
                    <td width="240"><input name="image" type="image" src="images/search.jpg" width="40" height="25"></td>

Open in new window



The form (on the search page) is set to POST.

After typing in the keywords, I am redirected to the results page.  The results page appears blank with no records matching my search.  

I decided to add a Response.Write of the SQL that displays the words I typed in the search fields.

Below is a portion of the results page:

<%@LANGUAGE="VBSCRIPT"%>
<%
Dim strSearch1, strSearch2, strSearch3
strSearch1 = Request.Form("search1")
strSearch2 = Request.Form("search2")
strSearch3 = Request.Form("search3")
%>
<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\inetpub\wwwroot\procedures\db\procedures.mdb"

sql= "SELECT * " _
& "FROM procedures " _
& "WHERE proc_info = '%"& Replace(strSearch1, "'", "''") & "%' OR " _
& "proc_info = '%"& Replace(strSearch2, "'", "''") & "%' OR " _
& "proc_info = '%"& Replace(strSearch3, "'", "''") & "%' "

'Response.Write sql

Recordset1.Source= sql
Recordset1.CursorType = 0 
Recordset1.CursorLocation = 2 
Recordset1.LockType = 1 
Recordset1.Open()

Recordset1_numRows = 0
%>

Open in new window


Why is the results page not displaying any results of my query? What is wrong with the code as I am not getting any errors?  Please advise with any recommendations or solutions.

Thank you.
0
Comment
Question by:arendt73
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 9
  • 4
  • +2
29 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 38764315
You're using wildcards, so I believe you are looking for partial matches versus exact matches.  Try using LIKE instead of =:

<%@LANGUAGE="VBSCRIPT"%>
<%
Dim strSearch1, strSearch2, strSearch3
strSearch1 = Request.Form("search1")
strSearch2 = Request.Form("search2")
strSearch3 = Request.Form("search3")
%>
<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\inetpub\wwwroot\procedures\db\procedures.mdb"

sql= "SELECT * " _
& "FROM procedures " _
& "WHERE proc_info LIKE '%"& Replace(strSearch1, "'", "''") & "%' OR " _
& "proc_info LIKE '%"& Replace(strSearch2, "'", "''") & "%' OR " _
& "proc_info LIKE '%"& Replace(strSearch3, "'", "''") & "%' "

'Response.Write sql

Recordset1.Source= sql
Recordset1.CursorType = 0 
Recordset1.CursorLocation = 2 
Recordset1.LockType = 1 
Recordset1.Open()

Recordset1_numRows = 0
%>

Open in new window

0
 
LVL 8

Expert Comment

by:Barry62
ID: 38764319
Could we see the entire code segment for the form?  What is the action of your form set to?
0
 

Author Comment

by:arendt73
ID: 38764330
Using LIKE instead of = returned all records in the results page.  I only need the records that match the keywords to display.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 61

Expert Comment

by:mbizup
ID: 38764355
Are they *Exact* matches?

<%@LANGUAGE="VBSCRIPT"%>
<%
Dim strSearch1, strSearch2, strSearch3
strSearch1 = Request.Form("search1")
strSearch2 = Request.Form("search2")
strSearch3 = Request.Form("search3")
%>
<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\inetpub\wwwroot\procedures\db\procedures.mdb"

sql= "SELECT * " _
& "FROM procedures " _
& "WHERE proc_info = '"& Replace(strSearch1, "'", "''") & "' OR " _
& "proc_info = '"& Replace(strSearch2, "'", "''") & "' OR " _
& "proc_info = '"& Replace(strSearch3, "'", "''") & "' "

'Response.Write sql

Recordset1.Source= sql
Recordset1.CursorType = 0 
Recordset1.CursorLocation = 2 
Recordset1.LockType = 1 
Recordset1.Open()

Recordset1_numRows = 0
%>

Open in new window

0
 

Author Comment

by:arendt73
ID: 38764362
Barry62, below is the Search page and the Results page.  

SEARCH PAGE CODE:
<%
Dim Recordset1
Dim Recordset1_numRows
[b]SEARCH PAGE CODE:[/b]
Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\inetpub\wwwroot\procedures\db\procedures.mdb"
Recordset1.Source = "SELECT * FROM procedures ORDER BY date_effective DESC"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
Recordset1_numRows = Recordset1_numRows + Repeat1__numRows
%>
<html>
<head>

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="styles/flmb.css" rel="stylesheet" type="text/css">
<title>Procedures</title></head>

<body leftmargin="0" rightmargin="0" topmargin="0">
<table width="100%" height="65" cellpadding="0" cellspacing="0">
  <tr> 
    <td bgcolor="#000000"><div align="center"></div></td>
  </tr>
  <tr>
    <td height="35" bgcolor="#F1F1F1"> 
      <div align="center"> 
        <table width="800" height="35" border="0" cellpadding="0" cellspacing="0" class="text-medium">
          <tr> 
            <td height="5" colspan="1"></td>
          </tr>
          <tr>
            <td height="25" colspan="1"><form action="results.asp" method="post" name="form1">
                <table width="800" height="20" border="0" cellpadding="0" cellspacing="0" class="text-medium">
                  <tr> 
                    <td width="210" valign="middle"><strong><font color="#666666">Search 
                      with up to three keywords</font></strong></td>
                    <td width="115"><input name="search1" type="text" class="text-medium" id="search1" size="15" value="<%= strSearch1 %>"></td>
                    <td width="115"><input name="search2" type="text" class="text-medium" id="search2" size="15" value="<%= strSearch2 %>"></td>
                    <td width="120"><input name="search3" type="text" class="text-medium" id="search3" size="15" value="<%= strSearch3 %>">
                    </td>
                    <td width="240"><input name="image" type="image" src="images/search.jpg" width="40" height="25"></td>
                  </tr>
                </table>
              </form></td>
          </tr>
          <tr>
            <td height="5" colspan="1"></td>
          </tr>
        </table>
      </div></td>
  </tr>
</table>
<% If Not Recordset1.EOF Or Not Recordset1.BOF Then %>
<table width="800" align="center" cellpadding="0" cellspacing="0" class="text-medium">
  <tr> 
    <td width="800" height="10" colspan="1"></td>
  </tr>
  <tr> 
    <td><table width="800" align="center" cellpadding="0" cellspacing="0" class="text-large">
        <tr> 
          <td width="40"><strong>File</strong></td>
          <td width="15">&nbsp;</td>
          <td width="190"><strong>Procedure Title</strong></td>
          <td width="15">&nbsp;</td>
          <td width="90" align="center"><strong>Effective</strong></td>
          <td width="15">&nbsp;</td>
          <td width="435"> <div align="center"><strong>Procedure Synopsis</strong></div></td>
        </tr>
      </table></td>
  </tr>
  <tr> 
    <td height="25"> <hr width="100%" size="2"></td>
  </tr>
  <tr> 
    <td><table width="800" cellpadding="0" cellspacing="0" class="text-large">
        <% 
While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF)) 
%>
        <tr> 
          <td width="40" valign="top"><a href="<%=(Recordset1.Fields.Item("proc_file").Value)%>" target="_blank" class="text-small"><font color="#0000FF">View</font></a></td>
          <td width="15" valign="top">&nbsp;</td>
          <td width="190" valign="top"><%=(Recordset1.Fields.Item("proc_title").Value)%></td>
          <td width="15" valign="top">&nbsp;</td>
          <td width="90" align="center" valign="top"><%=(Recordset1.Fields.Item("date_effective").Value)%></td>
          <td width="15" valign="top">&nbsp;</td>
          <td width="435" valign="top"><%=(Recordset1.Fields.Item("proc_info").Value)%></td>
        </tr>
        <tr> 
          <td height="30">&nbsp;</td>
          <td>&nbsp;</td>
          <td>&nbsp;</td>
          <td>&nbsp;</td>
          <td>&nbsp;</td>
          <td>&nbsp;</td>
          <td>&nbsp;</td>
        </tr>
        <% 
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  Recordset1.MoveNext()
Wend
%>
      </table></td>
  </tr>
</table>
<% End If ' end Not Recordset1.EOF Or NOT Recordset1.BOF %>
<% If Recordset1.EOF And Recordset1.BOF Then %>
<table width="800" border="0" align="center" cellpadding="0" cellspacing="0" class="text-medium">
  <tr> 
    <td><div align="center">
        <p>&nbsp;</p>
        <p><strong><font color="#CC0000"> THERE ARE NO ENTRIES IN THE PROCEDURES 
          DATABASE AT THIS TIME</font></strong></p>
      </div></td>
  </tr>
</table>
<% End If ' end Recordset1.EOF And Recordset1.BOF %>
</body>
</html>
<%
Recordset1.Close()
Set Recordset1 = Nothing
%>

Open in new window


RESULTS PAGE CODE:
<%@LANGUAGE="VBSCRIPT"%>
<%
Dim strSearch1, strSearch2, strSearch3
strSearch1 = Request.Form("search1")
strSearch2 = Request.Form("search2")
strSearch3 = Request.Form("search3")
%>
<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\inetpub\wwwroot\procedures\db\procedures.mdb"

sql= "SELECT * " _
& "FROM procedures " _
& "WHERE proc_info LIKE '%"& Replace(strSearch1, "'", "''") & "%' OR " _
& "proc_info LIKE '%"& Replace(strSearch2, "'", "''") & "%' OR " _
& "proc_info LIKE '%"& Replace(strSearch3, "'", "''") & "%' "

Response.Write sql

Recordset1.Source= sql
Recordset1.CursorType = 0 
Recordset1.CursorLocation = 2 
Recordset1.LockType = 1 
Recordset1.Open()

Recordset1_numRows = 0
%>

<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
Recordset1_numRows = Recordset1_numRows + Repeat1__numRows
%>

<html>
<head>

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="styles/flmb.css" rel="stylesheet" type="text/css">
<title>Results</title></head>

<body leftmargin="0" rightmargin="0" topmargin="0">
<table width="100%" height="65" cellpadding="0" cellspacing="0">
  <tr>
    <td bgcolor="#000000"><div align="center"></div></td>
  </tr>
</table>

<table width="800" align="center" cellpadding="0" cellspacing="0" class="text-medium">
  <tr> 
    <td width="800">&nbsp;</td>
  </tr>
  <tr> 
    <td><table width="800" align="center" cellpadding="0" cellspacing="0" class="text-large">
        <tr> 
          <td width="40"><strong>File</strong></td>
          <td width="15">&nbsp;</td>
          <td width="190"><strong>Procedure Title</strong></td>
          <td width="15">&nbsp;</td>
          <td width="90" align="center"><strong>Effective</strong></td>
          <td width="15">&nbsp;</td>
          <td width="435"> 
            <div align="center"><strong>Procedure Synopsis</strong></div></td>
        </tr>
      </table></td>
  </tr>
  <tr> 
    <td height="25"> 
      <hr width="100%" size="2"></td>
  </tr>
  <tr> 
    <td><table width="800" cellpadding="0" cellspacing="0" class="text-large">
        <% 
While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF)) 
%>
        <tr> 
          <td width="40" valign="top"><a href="<%=(Recordset1.Fields.Item("proc_file").Value)%>" target="_blank" class="text-small"><font color="#0000FF">View</font></a></td>
          <td width="15" valign="top">&nbsp;</td>
          <td width="190" valign="top"><%=(Recordset1.Fields.Item("proc_title").Value)%></td>
          <td width="15" valign="top">&nbsp;</td>
          <td width="90" align="center" valign="top"><%=(Recordset1.Fields.Item("date_effective").Value)%></td>
          <td width="15" valign="top">&nbsp;</td>
          <td width="435" valign="top"><%=(Recordset1.Fields.Item("proc_info").Value)%></td>
        </tr>
        <tr> 
          <td height="30">&nbsp;</td>
          <td>&nbsp;</td>
          <td>&nbsp;</td>
          <td>&nbsp;</td>
          <td>&nbsp;</td>
          <td>&nbsp;</td>
          <td>&nbsp;</td>
        </tr>
        <% 
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  Recordset1.MoveNext()
Wend
%>
      </table></td>
  </tr>
</table>

<% If Recordset1.EOF And Recordset1.BOF Then %>
<table width="800" border="0" align="center" cellpadding="0" cellspacing="0" class="text-medium">
  <tr> 
    <td><div align="center">
        <p>&nbsp;</p>
        <p><strong><font color="#CC0000"> THERE ARE NO ENTRIES IN THE PROCEDURES 
          DATABASE THAT MATCH YOUR SEARCH PARAMETERS. PLEASE TRY AGAIN. </font></strong></p>
      </div></td>
  </tr>
</table>
<% End If ' end Recordset1.EOF And Recordset1.BOF %>
</body>
</html>
<%
Recordset1.Close()
Set Recordset1 = Nothing
%>

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
ID: 38764373
If you are looking for exact matches you need to drop the % wildcards and use =.

If you are looking for partial matches, you need to use the % wildcards with LIKE instead of =.

What kind of data are you entering in these boxes?

Correction to the code in my last comment - assuming exact matches, so I'm using = without the % wildcard:

<%@LANGUAGE="VBSCRIPT"%>
<%
Dim strSearch1, strSearch2, strSearch3
strSearch1 = Request.Form("search1")
strSearch2 = Request.Form("search2")
strSearch3 = Request.Form("search3")
%>
<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\inetpub\wwwroot\procedures\db\procedures.mdb"

sql= "SELECT * " _
& "FROM procedures " _
& "WHERE proc_info = '"& Replace(strSearch1, "'", "''") & "' OR " _
& "proc_info = '"& Replace(strSearch2, "'", "''") & "' OR " _
& "proc_info = '"& Replace(strSearch3, "'", "''") & "' "

'Response.Write sql

Recordset1.Source= sql
Recordset1.CursorType = 0 
Recordset1.CursorLocation = 2 
Recordset1.LockType = 1 
Recordset1.Open()

Recordset1_numRows = 0
%>

Open in new window

0
 

Author Comment

by:arendt73
ID: 38764374
mbizup, yes, the words I entered in the search fields were taken directly from a record.
0
 

Author Comment

by:arendt73
ID: 38764405
mbizup, I am entering text words and occasionally dates (12/25/2010) into the search fields.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38764409
Did my last comment at http:#a38764373 make a difference?
0
 

Author Comment

by:arendt73
ID: 38764428
mbizup, I applied your code (http:#a38764373) and it returned zero results. Even if I used only one word, it returned no results.
0
 
LVL 54

Expert Comment

by:Scott Fell, EE MVE
ID: 38764482
@arendt73, will you please supply several rows of actual data we can  look at.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38764500
try changing your " OR " operator to " AND "
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38764509
I agree that some sample data would be useful -- both data representative of what you are entering in the search boxes and also data that you would see in the proc_info field.

From your last comment - it now sounds again like you are needing a LIKE search.

Try this, using only the first search box:


<%@LANGUAGE="VBSCRIPT"%>
<%
Dim strSearch1, strSearch2, strSearch3
strSearch1 = Request.Form("search1")
'strSearch2 = Request.Form("search2")
'strSearch3 = Request.Form("search3")
%>
<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\inetpub\wwwroot\procedures\db\procedures.mdb"

sql= "SELECT * " _
& "FROM procedures " _
& "WHERE proc_info LIKE '%"& Replace(strSearch1, "'", "''") & "%' " 
' & "proc_info LIKE '%"& Replace(strSearch2, "'", "''") & "%' OR " _
' & "proc_info LIKE '%"& Replace(strSearch3, "'", "''") & "%' "

'Response.Write sql

Recordset1.Source= sql
Recordset1.CursorType = 0 
Recordset1.CursorLocation = 2 
Recordset1.LockType = 1 
Recordset1.Open()

Recordset1_numRows = 0
%>

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
ID: 38764516
cap1,

--> try changing your " OR " operator to " AND "

The search is all on one field (proc_info) - so I think the OR operator is needed.

Edit:

I guess it depends on the desired results...

partial or exact search...

Containing all words from the search boxes or any word..  

Pretty unclear.
0
 

Author Comment

by:arendt73
ID: 38764569
mbizup,

I used the first search box along with your commented code and it gave a one record result - which is correct.  So it appears that the code is partially working, but with only one search box.

As requested, below is data taken from the proc_info memo field:

On 1/19/2011, we added a new role called "Funds Transport"

This document notifies a decision to no longer automatically set a deadline

This document provides information on implementation of company policies
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38764585
Are you looking for ALL of the words in your search boxes to be included in proc_info or ANY of the words in your search boxes?
0
 

Author Comment

by:arendt73
ID: 38764605
I am looking for all of the words entered into the search boxes.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38764613
Okay... then back to my first post (using LIKE instead of = ), and use capricorn1's  suggestion of AND instead of OR:
<%@LANGUAGE="VBSCRIPT"%>
<%
Dim strSearch1, strSearch2, strSearch3
strSearch1 = Request.Form("search1")
strSearch2 = Request.Form("search2")
strSearch3 = Request.Form("search3")
%>
<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\inetpub\wwwroot\procedures\db\procedures.mdb"

sql= "SELECT * " _
& "FROM procedures " _
& "WHERE proc_info LIKE '%"& Replace(strSearch1, "'", "''") & "%' AND " _
& "proc_info LIKE '%"& Replace(strSearch2, "'", "''") & "%' AND " _
& "proc_info LIKE '%"& Replace(strSearch3, "'", "''") & "%' "

'Response.Write sql

Recordset1.Source= sql
Recordset1.CursorType = 0 
Recordset1.CursorLocation = 2 
Recordset1.LockType = 1 
Recordset1.Open()

Recordset1_numRows = 0
%>

Open in new window

0
 
LVL 54

Expert Comment

by:Scott Fell, EE MVE
ID: 38764615
@mbizup in post 38764509

sql= "SELECT * " _
& "FROM procedures " _
& "WHERE proc_info LIKE '%"& Replace(strSearch1, "'", "''") & "%' " 
' & "proc_info LIKE '%"& Replace(strSearch2, "'", "''") & "%' OR " _
' & "proc_info LIKE '%"& Replace(strSearch3, "'", "''") & "%' "

Should that be
sql= "SELECT * " _
& "WHERE proc_info LIKE '%"& Replace(strSearch1, "'", "''") & "%'  OR _"
' & "proc_info LIKE '%"& Replace(strSearch2, "'", "''") & "%' OR " _
' & "proc_info LIKE '%"& Replace(strSearch3, "'", "''") & "%' "
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 200 total points
ID: 38764616
seen my comment above ?

try changing your " OR " operator to " AND "
0
 
LVL 54

Assisted Solution

by:Scott Fell, EE MVE
Scott Fell,  EE MVE earned 200 total points
ID: 38764620
I meant

Should that be
sql= "SELECT * " _
& "WHERE proc_info LIKE '%"& Replace(strSearch1, "'", "''") & "%'  OR  "_
' & "proc_info LIKE '%"& Replace(strSearch2, "'", "''") & "%' OR " _
' & "proc_info LIKE '%"& Replace(strSearch3, "'", "''") & "%' "
0
 

Author Comment

by:arendt73
ID: 38764651
padas, I applied your code, uncommented commented lines, and searched using two words and received the following error on the results page:

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

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '* WHERE proc_info LIKE '%provided%' OR proc_info LIKE '%notification%' OR proc_info LIKE '%%''.

Line 26
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38764668
@padas,

You need to include the table name (FROM procedures).


arendt73,

did you try my code at http:#a38764613 ?
0
 

Author Comment

by:arendt73
ID: 38764691
mbizup, the code at http:#a38764613 appers to be functioning correctly!  Thank you.  Now I need to sort by entry date.
0
 
LVL 54

Expert Comment

by:Scott Fell, EE MVE
ID: 38764693
In mssql I would  use charindex but I think in access you use instr and I would suggest giving this a try to see if it speeds up performance.



WHERE CHARINDEX(proc_info, 'value1') > 0 or CHARINDEX(proc_info, 'value2') > 0 or CHARINDEX(proc_info, 'value3') > 0

if instr

WHERE instr(proc_info, 'value1') > 0 or instr(proc_info, 'value2') > 0 or instr(proc_info, 'value3') > 0
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38764707
arendt73,,

you should be reading all the posts ...

i already told you to change " OR " to " AND"

at http:#38764500
0
 
LVL 61

Accepted Solution

by:
mbizup earned 1600 total points
ID: 38764711
I've added an ORDER BY clause here.  You may need to correct the actual field name if it is not 'entry date':

<%@LANGUAGE="VBSCRIPT"%>
<%
Dim strSearch1, strSearch2, strSearch3
strSearch1 = Request.Form("search1")
strSearch2 = Request.Form("search2")
strSearch3 = Request.Form("search3")
%>
<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\inetpub\wwwroot\procedures\db\procedures.mdb"

sql= "SELECT * " _
& "FROM procedures " _
& "WHERE proc_info LIKE '%"& Replace(strSearch1, "'", "''") & "%' AND " _
& "proc_info LIKE '%"& Replace(strSearch2, "'", "''") & "%' AND " _
& "proc_info LIKE '%"& Replace(strSearch3, "'", "''") & "%'  ORDER BY [entry date]"

'Response.Write sql

Recordset1.Source= sql
Recordset1.CursorType = 0 
Recordset1.CursorLocation = 2 
Recordset1.LockType = 1 
Recordset1.Open()

Recordset1_numRows = 0
%>

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
ID: 38764724
cap1,

<< i already told you to change " OR " to " AND" >>

Please note that I made a mention of that here too http:#a38764613 , referencing your post.

However simply changing OR to AND would not have done the trick... the = signs in the original post needed to be changed to LIKE as well.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38764810
:-0
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

650 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