We help IT Professionals succeed at work.

Query That doesn't include zero length strings and nulls in access

What is the proper Access Query/SQL Statement in Access to get a result set with no nulls and no zero length strings (in a specified field) in the result set. As an example below I'd like to find all contacts from tblContact with a title specified (i.e. no nulls or zero length strings):

SELECT DISTINCTROW tblContact.*
FROM tblContact
WHERE (((tblContact.Title) Is Not Null))
WITH OWNERACCESS OPTION;

Can't seem to get this one.. Thoughts?
Comment
Watch Question

Accountant/Developer
CERTIFIED EXPERT
Commented:
Try ...

WHERE Nz(tblContact.Title,"")<>""



ET
CERTIFIED EXPERT
Top Expert 2016

Commented:
try this

SELECT DISTINCTROW tblContact.*
FROM tblContact
WHERE tblContact.Title  & ""<>""
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
SELECT tblContact.*
FROM tblContact
WHERE tblContact.Title & "" <> ""

is one method.  Another is:

SELECT tblContact.*
FROM tblContact
WHERE LEN(tblContact.Title & "") = 0

Author

Commented:
Exactly what I needed! Thanks ET! Now to implement into a query builder....
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:

WHERE Nz(tblContact.Title, "") > ""

mx
Eric ShermanAccountant/Developer
CERTIFIED EXPERT

Commented:
<<<<<Now to implement into a query builder....>>>>>

Expr1: Nz([tblContact.Title],"")

Criteria Line: <>""



ET

Author

Commented:
hah thanks ET.. but it was actually an automatic code base query builder in VBA/MS Access that I needed to update.. Which I have done with your particular line it was just a little harder. Thanks again.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.