Access query using LIKE not working

Question

In Access I am trying to run a very simple query on the ‘Products’ table in the ‘Northwind’ database using ‘LIKE’ as below and cannot get it to work.

I will very much appreciate if someone will point out my mistake as clearly I am doing something wrong!

SELECT Products.ProductName, Products.CategoryID

FROM Products

WHERE (((Products.CategoryID) Like '*food'));

Best regards

Spike

Answer 1

I'm not sure what version of Northwinds you are using, but in the most recent, there is no CategoryID field in the Products table.

But , as XPS and George, suggest, a CategoryID field indicates that it may be an Autonumber foreign key. So you need to look in the table and find out what that field is and what datatype it is. If it is a number datatype, than a comparsion with text or using the LIKE operator won't work.

Als, when asking a question just saying it doesn't work doesn't help us help you. We need to know EXACTLY what happens when you run the query, any error messages or what.

Answer 2

Hi, I'm Elise, an Independent Advisor and I'd be happy to help with your issue.

This would only find records where Products.CategoryID ends with the 'food'.

If you want to find any record with the term 'food' in that field then you would change it to '*food*'

Please let me know if you need any further assistance.

Kind Regards,

Elise

Answer 3

Thank you for your help which has resolved the problem.

As the Categories field had text in it i assumed - incorrectly it was a text field when it is actually a number field and in the table design details the comment is 'Same entry as in the Categories table' which is an autonumber field and set as the Primary Key.

The query was actually producing nothing but now i know why so thanks again

very best

Spike

Answer 4

I'll bet you used one of those !@*&^ Lookup fields for CategoryID. I'll bet you SEE the words -- such as "Dog Food" -- when you LOOK at the table in datasheet view because Lookup fields are tricky and unreliable like that. They actually STORE the Foreign Key value, which is likely to be a number which is the same as the Primary Key in the Category table. They only DISPLAY the words.

While they show up with a gentle smile on their face, Lookup Fields in tables conceal trickery and ambiguity in their hearts.

If that is the case, and I could be wrong of course, the solution is to get rid of that Lookup Field in the table. Convert it to a standard field containing only the actual Foreign Key, which would be the number representing the Primary Key for the Categories.

If you do that, you'll have to adopt the approach xps350 described, but at least it will be transparent what is really going on.

Answer 5

Could it be that CategoryID is a numeric field?

In that case it problably is a foreign key to a table holding categories. If so, you have to include the catogory table in your query (joining it with Products) and apply the WHERE on the category text field.