By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,510 Members | 1,451 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,510 IT Pros & Developers. It's quick & easy.

How do I deselect null values from a list box?

Midzie
P: 25
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:
Expand|Select|Wrap|Line Numbers
  1. 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.
Feb 23 '12 #1

✓ answered by TheSmileyCoder

You could prevent them from occuring in your listbox by modifying the query:
Expand|Select|Wrap|Line Numbers
  1. 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 
  2.  
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.

Share this Question
Share on Google+
5 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
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.
Feb 23 '12 #2

Midzie
P: 25
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?
Feb 23 '12 #3

TheSmileyCoder
Expert Mod 100+
P: 2,321
You could prevent them from occuring in your listbox by modifying the query:
Expand|Select|Wrap|Line Numbers
  1. 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 
  2.  
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.
Feb 23 '12 #4

NeoPa
Expert Mod 15k+
P: 31,186
The following SQL should populate your ListBox control appropriately :
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT [Machine]
  2.               , '2' AS [lngSort]
  3. FROM            [Closed]
  4. WHERE           ([Machine] > '')
  5. UNION
  6. SELECT          'All'
  7.               , '1'
  8. ORDER BY        [lngSort]
NB. Smiley's advice should be followed though. It makes sense.
Feb 23 '12 #5

Midzie
P: 25
Hi Smiley/Neopa,

Thanks for the reply, my concern has been resolved. Thank you very much :-)
Feb 27 '12 #6

Post your reply

Sign in to post your reply or Sign up for a free account.