ASP/SQL issues

[em00guy]

I built a SQL statement that will search my db based on various criteria entered from a form. This search should return results that match the entries from all the non-empty form fields. One of these fields is a keyword search. The problem is when searching by keyword along with another field, the results seem to match either that other field or the keyword. Example: if searching by a certain criteria using one field yields 10 results and searching by a certain criteria using the keyword field yields 15 results, searching by both fields yields 25 results. This should not be!

This is a segment of the SQL statement containing the code for the key word search:
SQL="SELECT id, agency, city, state, zipcode " SQL=SQL&"FROM db "
SQL=SQL&"WHERE agency LIKE'%" & key & "%'OR address LIKE'%" & key & "%'OR room_suite LIKE'%" & key & "%'"

In the actual code there are several more OR operators and criteria. The rest of the statement defining the other search parameters are concantenated such as this:

SQL=SQL&"AND agency LIKE'%" & org & "%'"

If you wanna check out the working db go here http://www.whatsup.org/test/home.asp

I'm using an access dB if that helps.

[Woz]

EM, if I understand your question correctly,

"OR" gets more results (General), "AND" gets less results (Specific).

For example, we have three types of cars, Good, Bad and Ugly.
We have three car colours, Black, White and Grey

If you chose "where car like good OR colour like black" then you get all good cars regardless of colour, and all black cars regardless of type.

If you chose "where car like good AND colour like black" then you only get "GOOD BLACK" cars.

Get the idea? I think you would be better using "AND" all the way through.

Onya
Woz

[hasbeen]

Em...seemed to work okay for me on a cursory glance. Searching by keyword "storefront" and type "health" yielded a single result...I'll look some more, though.