Simple Query help

[natonstan]

Hello everyone, I need to make a query and i'm new to Access, basically I have a large table with numerous columns, basically I need a query that searches the table for a certain phrase (This phrase is the same always, I don't need any input fields or anything like that) Once it's found that phrase obviously those records would be displayed (most likely on a spreadsheet)

[June7]

Could either use the Find tool from ribbon or build query that has the same criteria under every field, but on separate lines so the OR operator is invoked (looks like stair steps).

Why do you need to search multiple fields for the same criteria? Sounds like data is not normalized.

[natonstan]

I've actually managed to build a query, now my next issue is I'm trying to display a record using two filters, the first Criteria is that it only shows records with a value in one of the fields, I've got that working fine but then I need it to also look in another field and show records that DON'T contain a phrase, the only problem is that the particular field doesn't just have that value in it, it has other values that aren't relevent to my query, I just need to focus on one particular word in that column, I've tried using the basic 'Not' and 'Not like' phrases but it doesn't seem to pick up on them at all, any ideas? it might help if I showed a screenshot of my table


The 'IPC' field is what i'm looking at, I need it to show records that don't contain 'BIG', the fact all of the values in there are seperated by ; is the issue in my opinion but I can't be 100%

[June7]

If the IPC field is a simple text type then the content is just a string of characters and LIKE and NOT LIKE with wildcard should work. If this is multi-value field (use of ; separator indicates it is not), that is entirely different issue.

Post the SQL statement for analysis.

[natonstan]

Here's the SQL:

SELECT tblAll.SUPR, tblAll.INV, tblAll.[HIERARCHY DESC], tblAll.IPC
FROM tblAll
WHERE (((tblAll.[HIERARCHY DESC])="Sealed Lead Acid Battery") AND (Not (tblAll.IPC)="BIG"));

[June7]

Have to use LIKE and wildcard.

WHERE (((tblAll.[HIERARCHY DESC])="Sealed Lead Acid Battery") AND (Not (tblAll.IPC) LIKE "*BIG*"));

[natonstan]

That worked! Thank you so much, that's exactly what I needed, i've learned something today. Thanks again