Do not return zero length entries

I have a table in Access which has a field called Company. I need to return all records where that filed is Not Null, but when I do, there are zero length records in there (“ “). How can I set the query to ignore the zero length records and only return the records which are actually not null or longer than zero length?

I tried Not Like " " along with Not Null but that did nothing to help.
QMBBAsked:
Who is Participating?
 
sdstuberCommented:
select * from yourtable where  len(yourfield) > 0


this will account for both zero-length and null
0
 
Dale FyeCommented:
select * FROM yourTable WHERE [yourField] & "" <> ""
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
How about this:

SELECT Table1.*, Table1.Company
FROM Table1
WHERE ((Not (Table1.Company) Is Null Or (Table1.Company)>""));

mx
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
OR

SELECT Table1.*
FROM Table1
WHERE (((Nz([Company],"")>"")=True));

And mod to first post:


SELECT Table1.*
FROM Table1
WHERE ((Not (Table1.Company) Is Null Or (Table1.Company)>""));
0
 
QMBBAuthor Commented:
Thank You!
0
 
Dale FyeCommented:
Personally, I use a function for this.

Public Function IsNullOrBlank(SomeValue as Variant) as Boolean

    IsNullOrBlank = (Len(SomeValue & "") = 0)

End Function

This is easy to read and debug.

SELECT * FROM yourTable WHERE IsNullOrBlank([FieldName]) = False
0
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.