Web Database Concept
     How We Work Together
     Web Database Sample
     Classic ASP Design Tips
     Bar Chart Tool
     Web Design Resources
     Access Database Sample

Classic ASP Design Tips
Search Criteria on Multiple Fields
These are opinions of J. Paul Schmidt, MBA.
No warranties are either expressed or implied.
To Use Paul's Services: Paul@Bullschmidt.com

Example on a page receiving a post of a SQL statement based on two listboxes (Rep and Customer) that might each have a value of "" (i.e. blank) to show all with no criteria restrictions.

' Get posted form vars.
Rep = Request.Form("Rep")
Customer = Request.Form("Customer")

' Set strSQL.
strSQL = "SELECT * FROM MyTable WHERE (1=1)"
If Rep <> "" Then
  strSQL = strSQL & " AND (Rep='" & Rep & "')"
End If
If Customer <> "" Then
  strSQL = strSQL & " AND (Customer='" & Customer & "')"
End If

Response.Write "strSQLWhere: " & strSQLWhere

And the (1=1) above is there as a placeholder (which doesn't affect the results since it is always true) as the SQL statement possibly may use AND with criteria after it or possibly the SQL statement may not have anything after it.  For example a SQL statement without the (1=1) like this would not work: SELECT * FROM MyTable WHERE AND Customer='API'

And to see something like this in action you can do the following:
- Go to www.bullschmidt.com/login.asp (the ASP Web database demo's login page)
- Click the Continue button
- On the Main Menu click the Invoices Edit button
- On the Invoices Edit Search Dialog's Customer listbox notice that you can choose a rep and/or customer and then click the Edit button to see all the invoices for that rep and/or customer

To Use Paul's Services:

Let's put some data on the Web!
Copyright © 2000-2018
J. Paul Schmidt, MBA
Freelance Web and Database Developer
All Rights Reserved

Privacy Policy