Query field if Like and Not Like? 1

[netrusher]

Below is the code I currently have in a field of my query.

How do I add to this field and not like "*exhaust*"? I am
looking for anything that has leak but not if it also
has exhaust

Leak Totals: Count(IIf([WorkUnitsFaultsMainTBL].[Problem] Like "*leak*",1,Null))

 

[SgtJarrow]

Have you tried:

Leak Totals: Count(IIf([WorkUnitsFaultsMainTBL].[Problem] Like "*leak*" AND [WorkUnitsFaultsMainTBL].[Problem] NOT LIKE "*exhaust*",1,Null))

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[netrusher]

Yep,

I tried that. It is still shows a leak that has the
word exhaust with it.

 

[dhookom]

You state "looking for" and "still shows" in two separate postings. Don't you mean "counts"? The records will always appear unless you are doing something to filter them out.

Duane
Hook'D on Access
MS Access MVP

 

[SgtJarrow]

I created a dummy table with one column and put sample data in it. I used this query:

SELECT IIf([field1] Like "*leak*" And [field1] Not Like "*exhaust*",1,Null) AS ItemValue
FROM Table4;

And it filters shows a 1 or NULL as you describe and I would expect. When I wrap that with a count, I get the correct count I expect from the filter...

SELECT Count(IIf([field1] Like "*leak*" And [field1] Not Like "*exhaust*",1,Null)) AS ItemValue
FROM Table4;

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[netrusher]

This worked

Leak Totals: Count(IIf([WorkUnitsFaultsMainTBL].[Problem] Like "*leak*" And [Problem] Not Like "*exhaust*",1,Null))