Solved

Filtering Blank Spaces Out Of A Column MS Access

Posted on 2008-10-15
12
232 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
[X]
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
  • 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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

Create CentOS 7 Newton Packstack Running Keystone

A bug was filed against RDO for the installation of Keystone v3. This guide is designed to walk you through the configuration for using Keystone v3 with Packstack. You will accomplish this using various repos and the Answers file.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

623 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