Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Filtering Blank Spaces Out Of A Column MS Access

Posted on 2008-10-15
12
229 Views
Last Modified: 2012-05-05
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]
0
Comment
Question by:npl77
  • 6
  • 5
12 Comments
 
LVL 75
ID: 22723298
SELECT Resources.[ID Manager]
FROM Resources
ORDER BY Resources.[ID Manager]
WHERE Not IsNull(Resources.[ID Manager])

mx
0
 
LVL 82

Expert Comment

by:hielo
ID: 22723302
try:
SELECT Resources.[ID Manager]
FROM Resources
WHERE Not isNull(Resources.[ID Manager])
ORDER BY Resources.[ID Manager]
0
 
LVL 75
ID: 22723322
ACtually thisL

SELECT Resources.[ID Manager]
FROM Resources
WHERE (((Resources.[ID Manager]) Is Not Null))
ORDER BY Resources.[ID Manager]
0
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 

Author Comment

by:npl77
ID: 22724187
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
 
LVL 75
ID: 22724237
Row Source is correct.

What is not working ?

mx
0
 

Author Comment

by:npl77
ID: 22724265
What happens is it simply gets the data like before without filtering the null fields.
0
 

Author Comment

by:npl77
ID: 22724315
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
 
LVL 75
ID: 22724360
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
 

Author Comment

by:npl77
ID: 22724432
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
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 500 total points
ID: 22724499
"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
 

Author Closing Comment

by:npl77
ID: 31506393
That did it! Thanks.
0
 
LVL 75
ID: 22724691
You are welcome ...

mx
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question