First of all, let me explain the database. There are eight fields that need to be search: last name, city, state, zipcode are four that the user will enter a value in. Four more fields are simple text fields with yes/no entered.

I'm trying to figure out the logic of the statement I'm going to use but wind up with no results each time I test it, so something's not right.

The search page has the four field that a user can enter parameters in the text box. There are also four checkbox the user can check. Example:

Enter last name
Enter City
Select State
Enter Zip Code
Check if FieldValue1 is Y
Check if FieldValue2 is Y
Check if FieldValue3 is Y
Check if FieldValue4 is Y

I want to be able to use this to search the database and display the results according to the following parameters:

All of the first four criteria are met but the field can be left blank AND the results are filtered by whther the FieldValues have "Y" in the fields.

So I want to be able to search for someone named Smith in Texas that has FieldValue1 checked. I also want to be able to search for everyone in Colorado that has FieldValue1 and FieldValue2 checked.

With me so far?

Here is the SQL statement I developed in Dreamweaver that doesn't work. It returns no results and is located on the results page.

<pre id=code><font face=courier size=2 id=code>&lt;%
Dim vols__lif
vols__lif = "1"
If (Request.Form("life" &lt;&gt; "" Then
vols__lif = Request.Form("life"
End If
%&gt;
&lt;%
Dim vols__edu
vols__edu = "1"
If (Request.Form("education" &lt;&gt; "" Then
vols__edu = Request.Form("education"
End If
%&gt;
&lt;%
Dim vols__fai
vols__fai = "1"
If (Request.Form("faith" &lt;&gt; "" Then
vols__fai = Request.Form("faith"
End If
%&gt;
&lt;%
Dim vols__fam
vols__fam = "1"
If (Request.Form("family" &lt;&gt; "" Then
vols__fam = Request.Form("family"
End If
%&gt;
&lt;%
Dim vols__lname
vols__lname = "1"
If (Request.Form("lname" &lt;&gt; "" Then
vols__lname = Request.Form("lname"
End If
%&gt;
&lt;%
Dim vols__city
vols__city = "1"
If (Request.Form("city" &lt;&gt; "" Then
vols__city = Request.Form("city"
End If
%&gt;
&lt;%
Dim vols__state
vols__state = "1"
If (Request.Form("state" &lt;&gt; "" Then
vols__state = Request.Form("state"
End If
%&gt;
&lt;%
Dim vols__zip
vols__zip = "1"
If (Request.Form("zip" &lt;&gt; "" Then
vols__zip = Request.Form("zip"
End If
%&gt;
&lt;%
Dim vols
Dim vols_numRows

Set vols = Server.CreateObject("ADODB.Recordset"
vols.ActiveConnection = MM_vol_STRING
vols.Source = "SELECT * FROM voldata WHERE (life = '" + Replace(vols__lif, "'", "''" + "' and education = '" + Replace(vols__edu, "'", "''" + "' and faith = '" + Replace(vols__fai, "'", "''" + "' and family = '" + Replace(vols__fam, "'", "''" + "') AND lname = '" + Replace(vols__lname, "'", "''" + "' or city = '" + Replace(vols__city, "'", "''" + "' or state = '" + Replace(vols__state, "'", "''" + "' or zip = '" + Replace(vols__zip, "'", "''" + "') ORDER BY lname ASC"
vols.CursorType = 0
vols.CursorLocation = 2
vols.LockType = 1
vols.Open()

vols_numRows = 0
%&gt; </font id=code></pre id=code>

Anyone can point our where I'm going wrong here?

Edited by - cbeaudry on 23 Mar 2004 22:39:49

Edited by - cbeaudry on 23 Mar 2004 22:40:42

Seems like you making it more complicated than need.
1. Create a table with all the states names and a table with all four fields.
2. Create a search page with a text box for the name, a text box for the state, a drop down box binded to the States table, and a text box for the zip code.
3. Create a results page with all for fields. Next in your query just have """where state = textbox 1 or name = textbox 2 or zip = textbox 3 or city = text box 4""" This will give you results in from all 4 or from 3 or 2 or 1 fields depending what the user enters.
PS: dont forget to put a % as a value in the drop down box so the user can select all states.

Edited by - Lilac Crafts on 25 Mar 2004 18:41:47