Ms Access Query Question

Hiroyuki Tamura
Hiroyuki Tamura used Ask the Experts™
on
I have two queries, contain_keyword, and doesNotContain_keyword, by using like and not like in criteria.
total record is 149, contain_keyword shows 115, and doesNotContain_keyword shows 5

149 /= 115 + 5

do you know why the query: "contain_keyword" does not show all records?

Database1.accdb
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Commented:
hi Hiroyuki,

I didn't look at your database ... running things on my computer and don't want to open anything else right now

just want to point out that without realizing it, you've also specified another criteria: Is Not Null

Assuming the field is text, in the Field cell that the criteria is under, try this:

MyField_: [MyField] & ""

that way, there will always be a value to compare

~crystal

Author

Commented:
Thank you, Crystal!

Sorry for my poor understanding.

I could see blank records are 29

149 /= 115 + 29 + 5
Distinguished Expert 2017

Commented:
Just to be clear, you can't distinguish ZLS (Zero Length Strings) from Nulls by looking at them.  And unless you have specified that your field should never contain a ZLS when you defined the table, they easily creep in.   Personally, I always set AllowZLS to NO for all text fields since I NEVER, EVER want ZLS to fool me into thinking a field is not null.  Before you can change the definition of the field in your table, you have to convert the ZLS to Null by using an update query.  MS has vacillated regarding the default of this property over the years and their current position is that they think it is easier for novices if they default automatically to ZLS rather than null and the experts can just take care of themselves and do what they want.

Crystal showed you a way to concatenate a ZLS with a text string to ensure that the string is never null but it can still contain a ZLS so you will also need a WHERE Clause.  In stead of Not Null, use

Where [MyField] & "" <> ""
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

John TsioumprisSoftware & Systems Engineer

Commented:
Just change the "doesNotContain_keyword"  to
SELECT test.Tags
FROM test
WHERE (((test.Tags) Not Like "*st_sf*")) OR (((test.Tags) Is Null));

Open in new window

To be on the safe side of ZLS....this handles this case
SELECT test.Tags
FROM test
WHERE (((test.Tags) Not Like "*st_sf*")) OR ((Len(Nz([Tags],""))="0"));

Open in new window

Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
You can use these two queries:

SELECT Tags
FROM test
WHERE Tags Like "*st_sf*";

Open in new window

and:
SELECT Tags
FROM test
WHERE Nz(Tags) Not Like "*st_sf*";

Open in new window

Will return records: 5 + 144 = 149

Author

Commented:
Thank you so much, everyone!
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial