2 Column Split Search Results from Access #1:

Code:

search.asp

<%option explicit%>

<%
dim conn, rs, rs2, strconn, strsql, strsql2

set conn = server.createobject("adodb.connection")
set rs = server.createobject("adodb.recordset")
set rs2 = server.createobject("adodb.recordset")
conn.open "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & server.mappath("test.mdb")

strsql = "select Name from test"
strsql2 = "select distinct Division from test"

rs.open strsql, conn, 2, 2
rs2.open strsql2, conn, 2, 2
%>

</head>
<body>

<%if request.querystring("Results") = "False" then%>
	<font color=#ff0000><b>Sorry, there are no matching records.</b></font>
<%end if%>

<table>
<tr>
<td class=font valign=middle><b>Name:</b> </td>
<td valign=middle>
<select name="Name">
<option value="">--Select a Name--</option>
<%if not rs.eof then
	do while not rs.eof%> 
		<option value="<%=rs("Name")%>"><%=rs("Name")%></option>
	<%rs.movenext
	loop
end if%>
</select>
</td>
</tr>

<td class=font valign=middle><b>Division:</b> </td>
<td valign=middle>
<select name="Division">
<option value="">--Select a Division--</option>
<%if not rs2.eof then
	do while not rs2.eof%> 
		<option value="<%=rs2("Division")%>"><%=rs2("Division")%></option>
	<%rs2.movenext
	loop
end if%>
</td>
</tr>

</form>

</font>

<%    
rs.close
rs2.close
conn.close 
set rs = nothing
set rs2 = nothing
set conn = nothing
%>

</body>
</html>

list.asp

Highlight

<%option explicit%>

<%
dim conn, rs, strconn, strsql
dim firstCount, secondCount, i

set conn = server.createobject("adodb.connection")
set rs = server.createobject("adodb.recordset")
conn.cursorlocation = 3
conn.open "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & server.mappath("test.mdb")
strsql = "select * from test where"

If request.querystring("Name") <> "" Then
strsql = strsql & " Name = '" 		&_ 
request.querystring("Name") & "' and "
End If

If request.querystring("Division") <> "" Then
strsql = strsql & " Division = '" 	& _
request.querystring("Division") & "' and "
End If

strsql = left(strsql,(len(strsql) - 5))
rs.open strsql,conn, 2, 2

if rs.eof then
	rs.close
	conn.close 
	set rs = nothing
	set conn = nothing
	response.redirect "search.asp?Results=False"
else%>

<table cellspacing="2" cellpadding="2" border="1">
		<tr>
			<td valign=top>
				<table cellspacing="2" cellpadding="2" border="0" nowrap>
					<%
					firstCount = rs.RecordCount 
					secondCount = round(firstCount/2,0)
					if secondCount < firstCount/2 then
						secondCount = secondCount + 1
					end if
					for i = 1 to secondCount
						Response.write "<tr>"
						Response.write "<td valign=top nowrap>"
						Response.write "<font face=arial size=2>"
						Response.write rs("Name") & " - " & rs("Division")
						Response.write "</font></td>"
						Response.write "</tr>"
						rs.movenext
					next 
					%>
				</table>
			</td>
			<td valign=top>
				<table cellspacing="2" cellpadding="2" border="0" nowrap>
					<%
					do while not rs.eof
						Response.write ""
						Response.write "<tr>"
						Response.write "<td valign=top nowrap>"
						Response.write "<font face=arial size=2>"
						Response.write rs("Name") & " - " & rs("Division")
						Response.write "</font></td>"
						Response.write "</tr>"	
					rs.movenext
					loop
					%>
				</table>
			</td>
		</tr>			   
	</table>
	</td>
	</tr>
	</table>
<%
end if

rs.close
conn.close 
set rs = nothing
set conn = nothing
%>