Access - "Is Not Null" Not working

I have a short text field - and "Is Not Null" does not work when I base a query off two tables that have the same field. I have changed the zero properties to say no in the table design, and trimmed to clean the entries.

Please advise why Is Not Null is not working.
exp vgAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Robert ShermanOwnerCommented:
For a text field that has "Allow Zero Length" set to No, a query criteria set to "Is Not Null" should return any fields where there are any characters in the field.  This would include strings that consist of only spaces.  If you are getting results in a query where you believe the field is empty, I would suggest clicking inside of the field and maybe bringing up the zoom box (shift-F2) to confirm that there are in fact no spaces in the field.

EDIT: Another quick test to check the content of the field in question would be to add another field in your query alongside the one you are questioning to show the length of that field.  For example, if the field name is "field1", add another column next to it in your query and put LengthOfField1: len([field1])

If there are any hidden characters in the field, you will then see a number in the length field.
Naitik GamitSoftware DeveloperCommented:
Look at this solution for Textbox null problem and null handling examples:
http://stackoverflow.com/questions/5662751/textbox-null-problem
Dale FyeOwner, Developing Solutions LLCCommented:
Chances are that the field contains a zero length string (ZLS).

Try a criteria that looks like:

WHERE (TRIM([FieldName] & "") = "") AND ([FieldName] IS NOT NULL)

This will identify records where you have a ZLS stored in the field.  You could replace those with NULLS by running an update query:

UPDATE yourTable SET [FieldName] = NULL
WHERE (TRIM([Fieldname] & "") = "")

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
exp vgAuthor Commented:
Thank you everyone
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.