Nulls won't filter out

I have a query in which I want to assign a value if the data is not null. However, even though the data appears to be null, when I use Is Not Null as a criteria, those records show up anyway. I've gone in and tried to delete anything in the fields, and they still show up. Any idea what can be causing this?

There are three possible values in the fields: A, B, TBD. So, as a work-around, I tried using:
AdminHearings: IIf([Admin Hearings Go To]>="a",[Associate - Not For Profit]). Normally, that would return any value with text in it. All of the Blank records are still showing up.
Who is Participating?
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
try a query like this

select * from table
where [fieldname] & ""<>""
Dale FyeConnect With a Mentor Commented:
what version of Access are you using?  It may be that what you are seeing in those cells is not NULL, but a zero length string.

you might try:

WHERE LEN(TRIM([Admin Hearings Go To] & "")) > 0

4charityAuthor Commented:
Thanks. Your solution also works. 2 ways to skin the same cat. I requested this to be reopened so I could reassign points (I awarded points before I saw your solution.)
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Rey Obrero (Capricorn1)Commented:
null is nothing, "" is zero length string
4charityAuthor Commented:
How does a zero length string get in there? And why can't you get it out?
Dale FyeCommented:
Until a value is entered, the value in a field will be NULL, unless you have a default value.

However, if you actually enter data into the field, and then delete it, what you generally get is a zero length string.

You can replace these with an update query:

UPDATE yourTable
SET [SomeField] = NULL
WHERE LEN(TRIM([SomeField] & "")) = 0
4charityAuthor Commented:
Perfect! This is a question that has haunted for me for a long time......... and I have never been able to find an answer that makes that much sense. These were actually imported from an Excel spreadsheet - that probably has something to do with it.
Dale FyeCommented:
Yeah, importing from Excel can cause these types of problems.  I generally import from excel into a "staging" table where every field is a string.  From there, I generally have a query that will identify anomolies in the data that has been imported, and allow me to address those (or tag them for later review).  Then, I run an append query that transforms all the data from the "staging" table into the final destination table and explicitly changes the data types to the appropriate type for the destination table.

This takes a bit more effort, but provides the ability to review and fix data prior to putting the data in your production table.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.