I am trying to do a search within query results:

My first search executes fine, but my second search uses the WHERE parameters of my first search.

For Instance if my first search was "Keyword: stein", and I would like to see which of those companies would be in "State: PA" then my sql should look like this:
select * from VIPP where (State like '%%PA%%') and (Keywords like '%%stein%%')

Instead I get this:
select * from VIPP where (State like '%%PA%%') and (State like '%%stein%%')

What am I missing?

Here is my code:
<title>Search within Results</title>
<%
u_where=request.form("u_where")
u_search=request.form("u_search")
u_prev_search=request.form("u_prev_search")
u_search_within=request.form("u_search_within")

if u_search <> "" then
if u_prev_search = "" then
u_prev_search=u_search
else

u_prev_search=u_prev_search &","& u_search
g_prev_search=split(u_prev_search,",")
num_inputted=ubound(g_prev_search)
end if

sql= "select * from VIPP where (" & u_where &" like '%%"& u_search & "%%') "

if u_search_within = "Yes" then
for counter =0 to num_inputted-1
sql=sql& "and (" & u_where & " like '%%"& g_prev_search(counter) & "%%') "
next
end if



accessdb="VIPP"
cn="DRIVER={Microsoft Access Driver (*.mdb)};"
cn=cn & "DBQ=" & server.mappath(accessdb)
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, cn

if rs.eof then
%>
No records were found
<%
else
rs.movefirst
do while Not rs.eof
%>
     <table> <td width="200" valign="top" align="left">

    <a href="<%=rs("Web_Address")%>" target="_blank"><%=rs("Supplier")%></a></td></table><br>
<%
rs.movenext
loop
end if
end if
%>

<form action="<%= request.servervariables("script_name") %>" method="post">
<select size="1" name="u_where">
                 <option value="Keywords" selected>Keywords</option>
                <option value="State">State</option>
             </select>
<input type="text" name="u_search" value="<%= u_search %>">
<br>
<%
if u_search <> "" then %>
<input type = "radio" name="u_search_within" checked value="No"> Search &nbsp;
<input type = "radio" name="u_search_within" value="Yes"> Search within results
<%
if u_search_within = "Yes" then %>
<input type = "hidden" name="u_prev_search" value="<%= u_prev_search %>">
<%
else %>
<input type = "hidden" name="u_prev_search" value="<%= u_search %>">
<% end if %>
<br>
<% end if %>
<input type="submit" value="Search">
</form>

<p>&nbsp;</p>
<%= sql %>
<br>

The SQL you are adding on for the previous search where value is using u_where. Should it be using u_search_within instead?

Peter
------------------------------------------------------
Work smarter, not harder.