Solved

Filtering Blank Spaces Out Of A Column MS Access

Posted on 2008-10-15
12
227 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

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 - Access MVP) 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Mysql Left Join Case 10 55
Report with several filters - Issue with query? 3 23
Binding recordsets to a form 6 26
access query to sql server 3 20
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…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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 …

778 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