kpurchase
asked on
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?
SELECT DISTINCTROW tblContact.*
FROM tblContact
WHERE (((tblContact.Title) Is Not Null))
WITH OWNERACCESS OPTION;
Can't seem to get this one.. Thoughts?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT tblContact.*
FROM tblContact
WHERE tblContact.Title & "" <> ""
is one method. Another is:
SELECT tblContact.*
FROM tblContact
WHERE LEN(tblContact.Title & "") = 0
FROM tblContact
WHERE tblContact.Title & "" <> ""
is one method. Another is:
SELECT tblContact.*
FROM tblContact
WHERE LEN(tblContact.Title & "") = 0
ASKER
Exactly what I needed! Thanks ET! Now to implement into a query builder....
WHERE Nz(tblContact.Title, "") > ""
mx
<<<<<Now to implement into a query builder....>>>>>
Expr1: Nz([tblContact.Title],"")
Criteria Line: <>""
ET
Expr1: Nz([tblContact.Title],"")
Criteria Line: <>""
ET
ASKER
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.
SELECT DISTINCTROW tblContact.*
FROM tblContact
WHERE tblContact.Title & ""<>""