SQL issue with access

[seanwpb]

i have the following. text4 is string i dont wont it to have and text2 and text3 i do want to have but text4 is showing up?

rs.Open "Select * From KnowledgeBase WHERE (Subject not LIKE '%" & Text4 & "%' OR AddDate not LIKE '%" & Text4 & "%' OR Category not LIKE '%" & Text4 & "%' OR Knowledge not LIKE '%" & Text4 & "%')" & _
" AND (Subject LIKE '%" & Text2 & "%' OR AddDate LIKE '%" & Text2 & "%' OR Category LIKE '%" & Text2 & "%' OR Knowledge LIKE '%" & Text2 & "%')" & _
" AND (Subject LIKE '%" & Text3 & "%' OR AddDate LIKE '%" & Text3 & "%' OR Category LIKE '%" & Text3 & "%' OR Knowledge LIKE '%" & Text3 & "%')" & _
" ORDER by Subject", conntemp, adOpenForwardOnly, adLockReadOnly

[smendoza]

well, you doing a SELECT * FROM so you are returning every column in the table. I would suggest typing the column names that you want the query to return. You'd do something like this.

Select FNAME, LNAME, ADDRESS1, CITY, STATE, ZIP
FROM customers

That would return only columns fname, lname, address, city, state, zip even though the table contains more than just those columns.

[Shaggy Hiker]

That's a difficult query to understand, and that's probably where the problem comes in. There is a problem in that first part. Suppose Text4 = Blue, and the fields are 1, 2, 3, and 4. If field 1 = Blue, then the query will do this:

Field 1 not like Blue? False

OR

Field 2 not like Blue? True

Or

Field 3 not like Blue? True

OR

Field 4 not like Blue? True

The result of all that is False OR True OR True OR True = True. Therefore, the first statement will always evaluate to true for every record where Text4 is not in all four fields. That's not what you want, as it is very unlikely that ANY record will be excluded by that. I believe that you just want to change the OR to AND for the comparisons for Text4.