Question about the WHERE statement

Hi all,
I have a page with 5 drop down menus. These drop downs act like filters for whatever data the user wants to retrieve from my database. Once submitted, it then goes to my asp page which displays the records based on what the user filtered from the previous page.

Here is the question.

How do i form my WHERE statement so that it returns the records based on the filters? Like If the user only decides to filter using 1 of the drop downs instead of all 5. If I use the AND statement, it trys to return all records that have a blank in them because one of the drop downs were left blank. Therefore returning no records. If I use the OR statement then it returns all records that meet drop down 1 and all records that meet drop down 2. What i want is someway to tell my SQL statement to only return records that meet drop down 1 and drop down 2 but when drop down 2 is blank, dont search for blank records in the database. Kind of like an And/Or statement. I hope that makes sense.

Here is my current SQL statement:

"SELECT YKPC_EXT_Orders.ACCOUNTANT, YKPC_EXT_Orders.FULL_NAME, YKPC_EXT_Orders.CL, YKPC_EXT_Orders.RF, YKPC_EXT_Orders.RFGL, YKPC_EXT_Orders.SITE, YKPC_EXT_Orders.PL, YKPC_EXT_Orders.[ACTUAL AMOUNT], YKPC_EXT_Orders.[TARGET AMOUNT], YKPC_EXT_Orders.[PRICE VAR], YKPC_EXT_Orders.[MATL USAGE], YKPC_EXT_Orders.[OTHER USAGE], YKPC_EXT_Orders.[PROCESS CHG VAR], YKPC_EXT_Orders.[VOL VAR] FROM YKPC_EXT_Orders WHERE (((YKPC_EXT_Orders.ACCOUNTANT)='" & Request.Form("txtAcct") & "')) OR (((YKPC_EXT_Orders.CL)='" & Request.Form("txtClient") & "'));"

thanks

the answer is to start your WHERE clause like this --

... WHERE 1=1

then in your script, you can test each variable for a value, and if a value has been selected, then you generate an AND like this --

... AND foo = 'bar'

that way, you do not have to wory about which value is the first one selected, or even if any were selected, because if none were selected, then WHERE 1=1 will return all the rows in the table

see also The "any" option in dynamic search SQL

thanks alot.
however thay article i found to be just a little confusing.

Does this modified SQL Line look alright??

strSQL = "SELECT YKPC_EXT_Orders.ACCOUNTANT, YKPC_EXT_Orders.FULL_NAME, YKPC_EXT_Orders.CL, YKPC_EXT_Orders.RF, YKPC_EXT_Orders.RFGL, YKPC_EXT_Orders.SITE, YKPC_EXT_Orders.PL, YKPC_EXT_Orders.[ACTUAL AMOUNT], YKPC_EXT_Orders.[TARGET AMOUNT], YKPC_EXT_Orders.[PRICE VAR], YKPC_EXT_Orders.[MATL USAGE], YKPC_EXT_Orders.[OTHER USAGE], YKPC_EXT_Orders.[PROCESS CHG VAR], YKPC_EXT_Orders.[VOL VAR] FROM YKPC_EXT_Orders" & _
" WHERE 1=1" & _
" If strCboAcct <> 'Show All' Then" & _
" AND (((YKPC_EXT_Orders.ACCOUNTANT)='" & strCboAcct & "'))" & _
" End If" & _
" If strCboCl <> 'Show All' Then" & _
" AND (((YKPC_EXT_Orders.CL)='" & strCboCl & "'))" & _
" End If"

I am getting an Unspecified Error whenever i try to load the page.

thanks

you have

" WHERE 1=1" & _
" If strCboAcct <> 'Show All' Then" & _

this makes your If test part of the sql string

what you have to do is terminate the string after WHERE 1=1, then do your if test, and for the true condition,

strSQL = strSQL & " AND (((YKPC_EXT_Orders.ACCOUNTANT)='" & strCboAcct & "'))"

r937, thank you for your help.

This is my new statement:

strAcct = Request.Form("txtAcct")
strClient = Request.Form("txtClient")

strSQL = "SELECT YKPC_EXT_Orders.ACCOUNTANT, YKPC_EXT_Orders.FULL_NAME, YKPC_EXT_Orders.CL, YKPC_EXT_Orders.RF, YKPC_EXT_Orders.RFGL, YKPC_EXT_Orders.SITE, YKPC_EXT_Orders.PL, YKPC_EXT_Orders.[ACTUAL AMOUNT], YKPC_EXT_Orders.[TARGET AMOUNT], YKPC_EXT_Orders.[PRICE VAR], YKPC_EXT_Orders.[MATL USAGE], YKPC_EXT_Orders.[OTHER USAGE], YKPC_EXT_Orders.[PROCESS CHG VAR], YKPC_EXT_Orders.[VOL VAR] FROM YKPC_EXT_Orders WHERE 1=1"

'Accountant Filter
If strAcct <> "Show All" Then
strSQL = strSQL & " AND (((YKPC_EXT_Orders.ACCOUNTANT)='" & strAcct & "'))"
End If

'CL Filter
If strClient <> "Show All" Then
strSQL = strSQL & " AND (((YKPC_EXT_Orders.CL)='" & strClient & "'))"
End If



Do you see anything wrong with this statement? For some reason it will not return any results anymore when searching for an accountant and leaving the client field's value = Show All.

I double checked using response.write to make sure i was bringing in the right field names and i am.

thanks

leave both fields as "show all" -- it should return all rows

if not, you have a problem in your ASP, and i don't do ASP, sorry