4charity
asked on
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
null is nothing, "" is zero length string
ASKER
How does a zero length string get in there? And why can't you get it out?
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
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
ASKER
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.
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.
This takes a bit more effort, but provides the ability to review and fix data prior to putting the data in your production table.
ASKER
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.)