Not Like statement not working as expected

[nick404]

Current SQL in a cbo query builder:

SELECT Product.Product, Product.Target
FROM Product
WHERE (((Product.Product) Not Like (([Product].[Product])="#5 ct CDW" And ([Product].[Product])="#5 ct HSD" 
And ([Product].[Product])="## ct HSD (Scrubby)" And ([Product].[Product])="42 ct HSD" And ([Product].[Product])="Chassis" And ([Product].[Product]) Like "7th G*")));

I do not want these products to appear on a combo box I have (the product name is either ambiguous or no longer run but do not want to delete and lose data connected). I am eliminating options for the user so they do not select these products that we don't use. However this SQL is not doing the trick. I still see all the options in the combo.

[June7]

The WHERE clause makes no sense. The expression on right of Not Like will return True or False so the result of the WHERE clause is:

WHERE Product.Product Not Like {True or False}

Also, the AND operator requires the value of that field to meet ALL the parameters to return a True. How can that ever be possible?

Does your value have the literal # character or are you trying to do a match on a digit in that position? I've never used # wildcard.

[nick404]

Yes matching a single digit in that position.

Would Or work better?

[June7]

Would still return a True or False which makes no sense for comparison to Product field.

What is the reason for excluding these records as options in combobox? These products are no long available? Could this list continue to grow requiring periodic edit of the combobox RowSource? Maybe Product table needs a yes/no field Active and apply filter on this field.

[nick404]

A few products were discontinued, and there was a lot of ambiguity with others.
For example, getting rid of '35 ct HSD' from the combo box because we want specified substrate number after. We now have 3 or 4 different '35 ct HSD' with substrate numbers after and do not want the user to just select '35 ct HSD' from the combo because they are too lazy to find the right substrate #.
How can I arrange this so my query will not return records with those product names in it?

I like the idea of an active field. I think I will end up doing that.