• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 285
  • Last Modified:

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.
0
4charity
Asked:
4charity
  • 3
  • 3
  • 2
2 Solutions
 
Rey Obrero (Capricorn1)Commented:
try a query like this

select * from table
where [fieldname] & ""<>""
0
 
Dale FyeCommented:
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

 
0
 
4charityAuthor Commented:
fyed:
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.)
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Rey Obrero (Capricorn1)Commented:
null is nothing, "" is zero length string
0
 
4charityAuthor Commented:
How does a zero length string get in there? And why can't you get it out?
0
 
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
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.
0
 
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.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now