Search Screen Queries

[cwilkey]

I have a search page that allows a user to enter search criteria such as a date range, author, etc. How can I setup multiple SQL statments based on the users input? For example, I may have 5 search fields and I want the query to be based on any of them being true, not just the first one. Here is what I've been trying and the query stops as soon as the first condition is true.

CODE:
"SELECT * FROM results WHERE ddate BETWEEN '" & startdate & "' AND '" & enddate & "'OR ddate BETWEEN '" & startdate & "' AND '" & enddate & "' AND author = '" & author & "'OR ddate BETWEEN '" & startdate & "' AND '" & enddate & "' AND news = 'Notified' OR ddate BETWEEN '" & startdate & "' AND '" & enddate & "' AND technical = 'Notified' OR ddate BETWEEN '" & startdate & "' AND '" & enddate & "' AND EAS = 'Notified' OR ddate BETWEEN '" & startdate & "' AND '" & enddate & "' AND Internet = 'Notified'OR ddate BETWEEN '" & startdate & "' AND '" & enddate & "' AND MasterControl = 'Notified'OR ddate BETWEEN '" & startdate & "' AND '" & enddate & "' AND Station = '" & station & "'OR station = '" & station & "'OR station = '" & station & "' AND author = '" & author & "'OR station = '" & station & "' AND mastercontrol = 'Notified'OR station = '" & station & "' AND technical = 'Notified'OR station = '" & station & "' AND news = 'Notified'OR station = '" & station & "' AND internet = 'Notified'OR station = '" & station & "' AND eas = 'Notified'OR mastercontrol = 'Notified' OR News = 'Notified' OR Technical = 'Notified' OR EAS = 'Notified'OR Internet = 'Notified'"

[buntine]

The use of the OR operator tells SQL that the query will return the record if any one of the clauses results to true. You may want to use the ANd operator.

I may have misunderstood, but at the moment, it seems you may be misinterpreting what is happening.

For example, I may have 5 search fields and I want the query to be based on any of them being true, not just the first one.

As far as I can tell, this is exactly what will happen with your query.

You may want to encapsulate each clause with brackets to enforce precedence.

Regards.

[cwilkey]

I guess to be more specific, the query needs to be based on ALL search criteria. For example, the user should be able to search by date range OR by date range AND station. I've found that my current SQL statement stops once the first criteria is true. So in this case:

CODE:
SELECT * FROM results WHERE ((ddate BETWEEN '" & startdate & "' AND '" & enddate '") OR ddate BETWEEN '" & startdate & '" AND '" & enddate & '" AND station = '" & station '" )

RESULTS:
SELECT * FROM results WHERE (ddate BETWEEN '06/01/2005' AND '06/02/2005' OR ddate BETWEEN '06/01/2005' AND '06/02/2005' AND station = 'KTIV' )

As a result of the first statement being true, the query stops.

[cwilkey]

I wish there was such a thing as a AND/OR statement where it will chech for both criteria based on user input.

[silverbullet24]

I do that a little different, i build my WHERE clause before the sql statement. For example, i might have a startdate and startime field in my search boxes. on the results page, i would do something like:

dim sWHERE
if request.form("startdate") <> "" then
sWHERE = "WHERE fldSTARTDATE = " & request.form("startdate")
end if
if request.form("starttime") <> "" then
sWHERE = sWHERE & " AND " & "fldSTARTTIME = " & request.form("starttime")
end if

[cwilkey]

Cool. I'll give that a try.