npl77
asked on
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]
HERE IS THE SQL TO BE CHANGED:
SELECT Resources.[ID Manager]
FROM Resources
ORDER BY Resources.[ID Manager]
try:
SELECT Resources.[ID Manager]
FROM Resources
WHERE Not isNull(Resources.[ID Manager])
ORDER BY Resources.[ID Manager]
SELECT Resources.[ID Manager]
FROM Resources
WHERE Not isNull(Resources.[ID Manager])
ORDER BY Resources.[ID Manager]
ACtually thisL
SELECT Resources.[ID Manager]
FROM Resources
WHERE (((Resources.[ID Manager]) Is Not Null))
ORDER BY Resources.[ID Manager]
SELECT Resources.[ID Manager]
FROM Resources
WHERE (((Resources.[ID Manager]) Is Not Null))
ORDER BY Resources.[ID Manager]
ASKER
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?
Row Source is correct.
What is not working ?
mx
What is not working ?
mx
ASKER
What happens is it simply gets the data like before without filtering the null fields.
ASKER
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?
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]
[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]
ASKER
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That did it! Thanks.
You are welcome ...
mx
mx
FROM Resources
ORDER BY Resources.[ID Manager]
WHERE Not IsNull(Resources.[ID Manager])
mx