no records are returned if some search fields are left blank

The query that you have now;

SELECT *
FROM tasks
WHERE request_date = #VARrequest_date#
AND requested_by = 'VARrequested_by'
AND job_ep = 'VARjob_ep'
AND deadline = 'VARdeadline'

will return results only if the request_date and the requested_by and the job_ep and the deadline equal the variables that you are sending to the statement.  In other words you are asking that all the fields be filled out in order to return results.

If you are just wanting the query to return results based on a single field then you would use OR.

SELECT *
FROM tasks
WHERE request_date = #VARrequest_date#
OR requested_by = 'VARrequested_by'
OR job_ep = 'VARjob_ep'
OR deadline = 'VARdeadline'

If you are wanting to use any combination of the fields then you would have to use additional code to identify your query.

For instance..,

Using Get as the method on your form, if someone filled in the fields requested_by and deadline, you would get simething that looks like this;

submittedpage.php?rb=John%20Doe&dl=050104

Where the url parameters rb and dl represent requested_by and deadline respectively.

You then set up a conditional statement that tracks how many of the possible combinations of the fields have been filled in. (the easiest way to do this is to give an inital value to all the fields and the ones that don't match the initial value are the ones you'll look for)

From there you would set up the values for the WHERE clause of your query by combining all of the filled in fileds into a string that's passed to the variable that holds the query.

sqlquery = "SELECT *
                  FROM tasks
                  WHERE "

This is what you get from the conditional statement

temp1 = "requested_by = 'VARrequested_by'"
temp2 = "deadline = 'VARdeadline'"

Now concat

sqlquery .= temp1
sqlquery .= "AND"
sqlquery .= temp2

This will give you the complete query;

sqlquery = "SELECT *
                  FROM tasks
                  WHERE requested_by = 'VARrequested_by'
                  AND deadline = 'VARdeadline'";

Now obviously this is psuedo code.  You didn't say what language you were using, but you should get the idea.

What you seem to be proposing is a method for manipulating the data in different ways, the most relevent being combining fields. My needs are very simple though. I want to retrieve records that contain all the data entered. The more information the user enters, the less records retrieved. If I use OR and submit a blank form, it retrieves all the records, but the opposite should happen since I didn't enter any data. It seems to be treating blank fields as wildcards when it should be NULL or something else that would ignore it.

For now, I think entering a default value in the fields will do the trick, but its not very clean or elegant. It's also tricky because I have 'Text Areas' that could potentially contain the default text.


John

Oh, and I'm using ASP VBScript with an Access DB in Dreamweaver MX.

John

then on the search Result page Replace the NULL values with something, rather then setting some default value for textarea.

like this

VARrequested_by = Request("VARrequested_by")

if LEN(VARrequested_by) < 0 Then

VARrequested_by = "Not For Search"

End if

johnmarshall,

I think you misunderstood what I was proposing.  My fault.

I'm not suggesting that you manipulate the data but rather the query statement.  Instead of using a single blanket query that could return inaccurate results you simple make the query more dynamic so it will produce the results that you require based on the user input on your form.  This way your are insured to retrieve accurate results.

Well, I have solved this problem and it was very simple. I changed this:

AND field = 'VARfield'

to this:

AND field LIKE '%VARfield%'

...and now it accepts empty fields as a valid value. I'm leaving this question open