Solved

Filtering Blank Spaces Out Of A Column MS Access

Posted on 2008-10-15
12
225 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
 

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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
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…
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 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…

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now