Multiple Conditions??/

[cwilkey]

I'm trying to create a search page that will allow a user to search by many fields OR by any cobination of the fields. I'm having a hard time setting up my SQL statement. Is there a way to do a multiple conditioned if statement, such as:

If condition AND condition then
statement
End If

[lmf232s]

you may have to get tricky and think about it.
You could try it like this (there are many ways to do this)

Dim SQL
DIm SQLwhere

SQL = "SELECT myFIELDS FROM myTABLES"

if txtPartNum <> "" then
SQLwhere = SQLwhere & " AND myFIELD = '" & txtPartNum & "' "
end if

if cboState <> "" then
SQLwhere = SQLwhere & " AND myFIELD = '" & cboState & "' "
end if

If SQLwhere <> "" then
SQL = SQL & SQLwhere
end if

SQL = SQL & " ORDER BY ?, ?, ? ASC "


or you could create a sub that you could call and build the where statement
in the sub. Again this is just an idea, there are several ways to go about this, and im sure everyone does it alittle bit differently.

[buntine]

You will need to put a where clause in there. And make sure that the AND operator is actually needed. :)

Dim SQL
DIm SQLwhere

SQL = "SELECT myFIELDS FROM myTABLES"

if txtPartNum <> "" then
SQLwhere = SQLwhere & "myFIELD = '" & txtPartNum & "' "
end if

if cboState <> "" then
If SQLQhere <> "" Then SQLWhere = SQLWhere & "AND "
SQLwhere = SQLwhere & "myFIELD = '" & cboState & "' "
end if

If SQLwhere <> "" then
SQL = SQL & " WHERE " & SQLwhere
end if

SQL = SQL & " ORDER BY ?, ?, ? ASC "

Regards.

[lmf232s]

ya that where clause might help, thats what i get from hand typing and not actually executing code, i overlook stuff. :)

Another thing is you might want to do if you have problems is
Response.write SQL
Right before you try to open it, that way you can see the query that you
are tyring to execute, which will help you debug your query if you have any
problems.

[buntine]

Yeh. I probably missed a few vital points, too!

[silverbullet24]

when i do searches like this i do it the same way but i add the "AND" to the end of the statement and then trim it off at the end if it appears. like this:

dim sWhereClause, sSQL
sWhereClause = ""
If request.form("field1") <> "" Then
SWhereClause = SWhereClause & "tblfield1 = '" & request.Form("field1") & "' AND "
End If
If request.form("field2") <> "" Then
SWhereClause = SWhereClause & "tblfield2 = '" & request.Form("field2") & "' AND "
End If
If request.form("field3") <> "" Then
SWhereClause = SWhereClause & "tblfield3 = '" & request.Form("field3") & "' AND "
End If
If Right(SWhereClause,4) = "AND " Then
SWhereClause = Left(SWhereClause, Len(SWhereClause) -4)
sSQL = "SELECT * FROM table WHERE " & sWhereClause
Else
sSQL = "SELECT * FROM table WHERE " & sWhereClause
End If

[cwilkey]

Can I do something like this:

If field <> "" & anotherfield <> "" Then
Statement
End If

[silverbullet24]

you can but you have to use the AND operator

If (field <> "") AND (anotherfield <> "")Then
Statement
End If