Searching a Database with multiple fields

Here is the thing. I am in need of a search script that can search three columns using an And/Or feature automatically.

To simply put, I got an Access Database, with three columns (field1,field2,field3).

I have a forum with three fields (field1,field2,field3).

If some only field1 to search, it will be just

Select * from tableName where field1 = '%" & Request.Form("field1") & "%'"

If some one decides to search using field1 and field 2

Select * from tableName where field1 = '%" & Request.Form("field1") & "%' and field1 = '%" & Request.Form("field2") & "%'"

Please ASSIST and THANKS in advance...

Shrini

How about something like:

strSQL = "Select * from tableName "

If Request.Form("field1") <> "" and Request.Form("field2") <> "" and Request.Form("field3")<>"" Then
    strSQL = strSQL & " where "

If Request.Form("field1") <> "" then
    strSQL = strSQL & "field1 = '%" & Request.Form("field1") & "%'"
    if Request.Form("field2")<> "" or Request.Form("field3")<> "" then strSQl = strSQL & " and "
End if

 

if Request.Form("field2")<> "" then
    strSQL = strSQL & "field2 = '%" & Request.Form("field1") & "%'"
    if Request.Form("field3")<> "" then strSQL = strSQL & " and "
End ifif Request.Form("field3")<> "" then
    strSQL = strSQL & "field3 = '%" & Request.Form("field1") & "%'"
End if

End if

Is that sort of what you are lookig for?

Well I figured out a way to do that. It took little technical thinking. But stuck with another SQL Statement problem which is working little strange when I try to right it.

SELECT
user.id,
user.username,
user.password,
user_info.fullname
FROM user
INNER JOIN
user_info ON
user.id = user_info.id

I executed the statement with my dbConn and wrote in the page using the following...

do while not rs.eof
Response.Write id & " | " & username & " | " & password & " | " & fullname
rs.movenext
loop

There are 12 records, that match in the two tables, using the id column.

But when it writes, it takes the first record and writes it 12 times. Any Ideas...

Thanks in Advance,

Shrini