Filtering Blank Spaces Out Of A Column MS Access

I have a column in one of my access tables that I use populate a combobox in an access form. The problem is I column has null entries in it which when opening the combobox connected to that column I get blank spaces in it. What is the best way to get these blank spaces out of the combobox in the form.

HERE IS THE SQL TO BE CHANGED:

SELECT Resources.[ID Manager]
FROM Resources
ORDER BY Resources.[ID Manager]
npl77Asked:
Who is Participating?
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"so it checks for either zero length strings or null values?"

My last post does that.

Here is the generic Syntax:

SELECT Table1.FIELD1
FROM Table1
WHERE (((Nz([FIELD1],"")>"")=True))
ORDER BY Table1.FIELD1;

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
SELECT Resources.[ID Manager]
FROM Resources
ORDER BY Resources.[ID Manager]
WHERE Not IsNull(Resources.[ID Manager])

mx
0
 
hieloCommented:
try:
SELECT Resources.[ID Manager]
FROM Resources
WHERE Not isNull(Resources.[ID Manager])
ORDER BY Resources.[ID Manager]
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:
ACtually thisL

SELECT Resources.[ID Manager]
FROM Resources
WHERE (((Resources.[ID Manager]) Is Not Null))
ORDER BY Resources.[ID Manager]
0
 
npl77Author Commented:
In the form I put the sql under the "Row Source" property of the textbox. It isnt working. Is there some other property of the textbox that needs to be set?
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Row Source is correct.

What is not working ?

mx
0
 
npl77Author Commented:
What happens is it simply gets the data like before without filtering the null fields.
0
 
npl77Author Commented:
When I put the query in the "Queries" and run the query its returning the blank rows still. Maybe the rows are not null just blank...How can I add to the query to filter blanks rows out too?
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Is this the field that has the Nulls:

[ID Manager]

?

If not, what Field in the table ?

Maybe they are really Zero Length Strings ... try this:

SELECT Resources.[ID Manager]
FROM Resources
WHERE Nz([Resources.[ID Manager],"")>"")=True
ORDER BY Resources.[ID Manager]

0
 
npl77Author Commented:
I think theres something wrong with that query syntax. Can you check it again and also add the null filter too it, so it checks for either zero length strings or null values?
0
 
npl77Author Commented:
That did it! Thanks.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
You are welcome ...

mx
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.