How do I deselect null values from a list box?

[Midzie]

Hi all, I have here a list box bound to my table Closed.Machine, but there are some null values from my table. So my list box have these values: ALL, ,01,02,03, and when I chose "null" the value next to it will appear in my report. Is there a way to exclude null values in my list box? If none, is there a way too that those records with null value from my closed.machine will appear in my query? Here's my code from my list box Row Source property:

select "All", '1' as lngsort from Closed union select distinct [Closed].[Machine], "2" as lngsort from Closed where ([Closed].[Machine] is not null) order by lngsort

Thanks in advance.

[TheSmileyCoder]

It seems there are some inconsistensies in your describtion. As far as I can see your query should not return nulls. I also think you need to find out why you even have nulls in your data (assuming its your data, generated internally, and not coming from an outside source). Nulls often (but not always) indicate you have some failed logic somewhere.


So why do you have null values? Are you even sure its a genuine null and not an empty string? Since if your seing it in your listbox, I am guessing it might be that you have managed to place an empty string in the field.

[Midzie]

I double check the existing table and I found that those are empty values not null values. How do I handle empty values in lstbox?

[TheSmileyCoder]

You could prevent them from occuring in your listbox by modifying the query:

select "All", '1' as lngsort from Closed union select distinct [Closed].[Machine], "2" as lngsort from Closed where ([Closed].[Machine] is not null and [Closed].[Machine]<>"") order by lngsort 
 

You still need to wonder why you even have empty strings in your database. The default behavior for a textbox when you delete all text in it, is to return to null, not to leave it as a empty string. You can through code force empty strings to be stored as opposed to the default behavior.

You can also for a text field specify that it is not allowed to accept a zero length string (You do this in table design view). For most applications I see only bad coming from allowing zero-length strings, but it is ultimately your call, as I dont know what your requirements are.

[NeoPa]

The following SQL should populate your ListBox control appropriately :

SELECT DISTINCT [Machine]
              , '2' AS [lngSort]
FROM            [Closed]
WHERE           ([Machine] > '')
UNION
SELECT          'All'
              , '1'
ORDER BY        [lngSort]

NB. Smiley's advice should be followed though. It makes sense.

[Midzie]

Hi Smiley/Neopa,

Thanks for the reply, my concern has been resolved. Thank you very much :-)