Filtering Records with WHERE clause

Good question, I ran into the same problem when filtering data and heres what I did:
\include\variables.asp (this is where my filter is)
gstrSQL = "SELECT [ID], [Member], [Priority], [TicketType], [Ticket], [Address_Number], [Address_Street], [County], [DueBy], [Revision] From [dbo].[ArcTixTBL] where [Priority] = '52' and [DueBy] > '1/1/2004' order by [DueBy]"
and this is what I had to do to get the search engine not to error out performaing a search after the start page
\list.asp
under the regular serach code. I changed this line of code to trim the data out of my *variable.asp line:
if Request.Form("SearchField")<>"AnyField" then

strSQL = left(strSQL,215) & " and " & AddWrappers(Request.Form("SearchField")) & StrWhere(Request.Form("SearchField"), strSearchFor, strSearchOption)+" order by [DueBy]"
hope this helps

Thanks for your input.
Here is a simple approach that also worked for me:

1. I replaced the generated code with the following code. This simply replaces the WHERE clause with AND
   
2. I deleted the "AnyField" option from the Search Form. That makes the ELSE section to never be executed
   =====================================================
   
   if Request.Form("SearchField")<>"AnyField" then
   
   strSQL = strSQL & " and " & AddWrappers(Request.Form("SearchField")) & StrWhere(Request.Form("SearchField"), strSearchFor, strSearchOption, "")
   
   else
   
   THIS SECTION CAN BE DELETED AS IT WILL NOT BE EXECUTED
   
   end if
   =====================================================
   I have 2 OTHER issues with the Search Form:
   
3. After a search the Search Form Options reset to their original settings. So, when there are lots of records the user may not remember what was searched. A message that stays on the screen indicating the displayed query would be nice.
   
4. If in your search you use the "field =, <, or > " on a date field, and do not enter a properly formated date, it generated an error. That's not good. The program should validate the field or return a list with no records.
   Does anyone know of any solutions or workarounds?
   Thanks.
   James

I won't recommend to add WHERE clause to the SQL statement. Here is the more reliable way to implement this.
Lets say you have table Orders in MS Access. Open this database in MS Access, create new query, switch to SQL view and type

select * from Orders where LoadStatus='A'

Save it as qryOrders, run ASPRunner and use qryOrders as a datasource. This is it.

Thanks for your input.

1. I replaced the generated code with the following code. This simply replaces the WHERE clause with AND
   
2. I deleted the "AnyField" option from the Search Form. That makes the ELSE section to never be executed
   =====================================================
   
   if Request.Form("SearchField")<>"AnyField" then
   
   strSQL = strSQL & " and " & AddWrappers(Request.Form("SearchField")) & StrWhere(Request.Form("SearchField"), strSearchFor, strSearchOption, "")
   
   else
   
   THIS SECTION CAN BE DELETED AS IT WILL NOT BE EXECUTED
   
   end if
   =====================================================
   I have 2 OTHER issues with the Search Form:
   
3. After a search the Search Form Options reset to their original settings. So, when there are lots of records the user may not remember what was searched. A message that stays on the screen indicating the displayed query would be nice.
   
4. If in your search you use the "field =, <, or > " on a date field, and do not enter a properly formated date, it generated an error. That's not good. The program should validate the field or return a list with no records.
   Does anyone know of any solutions or workarounds?
   Thanks.
   James

James,
I've implemented your solution and the error -2147217900 doesn't appear but the search "engine" doesn't search data, there is no expected data result, random data are given back. <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=1320&image=1&table=forumreplies' class='bbc_emoticon' alt=':(' />
Herewith my SQL query:

SELECT dbo.TblWarehouse.Warehouse, dbo.TblWarehouse.Description, dbo.TblWarehouseItem.InternalItemNo, dbo.TblItem.ItemDescription1, dbo.TblCompanyItemPrice.CurrentCostPrice, dbo.TblWarehouseItem.OnHandQty, dbo.TblWarehouseItem.ReorderLevel

FROM ((dbo.TblWarehouse INNER JOIN dbo.TblWarehouseItem ON dbo.TblWarehouse.Warehouse = dbo.TblWarehouseItem.Warehouse) INNER JOIN dbo.TblCompanyItemPrice ON dbo.TblWarehouseItem.InternalItemNo = dbo.TblCompanyItemPrice.InternalItemNo) INNER JOIN dbo.TblItem ON dbo.TblCompanyItemPrice.InternalItemNo = dbo.TblItem.ItemNo

WHERE (((dbo.TblWarehouseItem.OnHandQty)<>0)) OR (((dbo.TblWarehouseItem.ReorderLevel)<>0)) order by TblWarehouse.Warehouse asc"
The search option is built for:

dbo.TblWarehouse.Warehouse,

dbo.TblWarehouseItem.InternalItemNo,

dbo.TblItem.ItemDescription1
best regards,
Adam.

Adam,
I do not know about your code. Mine does work. You can see it here:
My Test Webpage
In any event, I like the recommendation made by Sergey. It seems cleaner and less work. I'll give it a try.
Good luck.
James