problem with query

harpua · January 24th, 2005, 12:05 PM

I'm hoping somebody can please help me with this query problem. I am wanting it to only search in only the states
where they have any of the particular criteria, such as in this example, it should only search Kentucky or Minnesota and
have displays and plastic bags. What is happening is that it is returning all the states with any of the criteria, it seems to
be ignoring the State IN statement.

strSQL = "SELECT Vendor_ID, CompanyName, FacilityName, Address, City, State, Zipcode " &_
            "FROM Table_02 WHERE " &_
            " SFOffsetPrinting_Less40 = 0 " &_
            " OR PrePress = 0 " &_
            " OR Displays = 1 " &_
            " OR PlasticBags = 1 " &_
            " OR SpecialityItems = 0 " &_
            " OR Forms = 0 " &_
            " OR Fulfillment = 0 " &_
            " AND State IN (" & strSearchStates & ");"

My response.Write statement:
SELECT Vendor_ID, CompanyName, FacilityName, Address, City, State, Zipcode FROM Table_02
WHERE SFOffsetPrinting_Less40 = 0 OR PrePress = 0 OR Displays = 1 OR
PlasticBags = 1 OR SpecialityItems = 0 OR Forms = 0 OR Fulfillment = 0
AND State IN ('Kentucky', 'Minnesota');

Thanks in Advance
Mike

shahchi1 · January 24th, 2005, 12:36 PM

When you use OR it mean any of the listed condition is true it will return result,

create a dynamic SQL WHERE clause based on the required criteria

something like this..

strSQL = "SELECT Vendor_ID, CompanyName, FacilityName, Address, City, State, Zipcode " &_
         "FROM Table_02 WHERE " &_
         "State IN (" & strSearchStates & ") "
IF (based on the input, if this need to be a criteria) THEN
    strSQL = strSQL & " AND SFOffsetPrinting_Less40 = 0 "
End IF
IF (based on the input, if this need to be a criteria) THEN
    strSQL = strSQL & " AND PrePress = 0 "
End IF
IF (based on the input, if this need to be a criteria) THEN
    trSQL = strSQL & " AND Displays = 1 "
End IF
IF (based on the input, if this need to be a criteria) THEN
    strSQL = strSQL & " AND PlasticBags = 1 "
End IF
IF (based on the input, if this need to be a criteria) THEN
    strSQL = strSQL & " AND SpecialityItems = 0 "
End IF
IF (based on the input, if this need to be a criteria) THEN
    strSQL = strSQL & " AND Forms = 0 "
End IF
IF (based on the input, if this need to be a criteria) THEN
    strSQL = strSQL & " AND Fulfillment = 0 "
End IF