ListBox row source returning multiple 'blanks' (Access2007)

[AOB]

Hi there,

I have a list box on a form which uses the following SQL to pull the list items from a table :

SELECT DISTINCT tblMyTable.MyField FROM tblMyTable ORDER BY tblMyTable.MyField;

For some reason, during testing, the first two items on the list are blanks. I am quite happy for one blank to be returned at the top of the list (as this would easily identify for the user any records for which this field has not yet been completed, which is a good thing) but I can't understand why it would appear twice?

Why doesn't the DISTINCT command ensure any blank entries only appear once?

Incidentally, I have a subform which populates on the back of selections from the list boxes, so I can quickly see the corresponding records which generate these blanks. What's puzzling me is that the same records show for both blanks on the list - suggesting it is the same value repeating itself (and not, say, "" vs " ", for example, which was what I originally suspected...)

Any suggestions??

Thanks

AOB

 

[rzw0wr]

Try filtering with a WHERE clause and remove the DISTINCT.

Dale

 

[AOB]

Thanks Dale - not quite sure how to retrieve unique / distinct values from a list without using DISTINCT, can you show me what you mean, using my original SQL as a starting point?

 

[JHB]

Maybe it is a space.

SELECT DISTINCT tblMyTable.MyField FROM tblMyTable WHERE tblMyTable.MyField <>" " ORDER BY tblMyTable.MyField;

 

[AOB]

Thanks JHB

It's weird, it's definitely not a space as I've checked the records in the table datasheet view.

I can only assume it's instances of both nulls and zero-length strings (appearing as two 'distinct' values in the list box)

What puzzles me is that filtering on both values gives the same subset of records (I would expect one to give me the records where that field has a null value, and the other to give me the records where that field is a zero-length string - but both give me the exact same subset? Weird?)

In the end I've just excluded the nulls :

SELECT DISTINCT tblMyTable.MyField FROM tblMyTable WHERE tblMyTable.MyField Is Not Null
ORDER BY tblMyTable.MyField;

I'd prefer it if the nulls were included as well, but viewed in the same way as blanks (i.e. one single option in the listbox) rather than as two separate list items (as how can the user tell which one represents the nulls and which one represents the ZLS's)

But for now, this will have to suffice I guess...

Thanks everybody for your help - if anybody else has come across this problem and has found a way of representing nulls & ZLS's as a single item in a listbox, I'd be glad to hear it!

AOB

 

[rzw0wr]

not quite sure how to retrieve unique / distinct values from a list without using DISTINCT, can you show me what you mean, using my original SQL as a starting point?

SELECT  tblMyTable.MyField FROM tblMyTable  WHERE What youWant = WhatYou WantTo Show.

If you use a WHERE clause you are filtering the data by what you want/do not want, to to be returned.

Example: WHERE MyID = tblSomeThing_ID
You don't need DISTINCT because there should not be duplicates.

Dale

 

[Pat Hartman]

Sounds like one or more records have "" (ZLS - Zero Length String) as a value and others have Nulls. I always set my text fields to not allow ZLS. The only time you might run into trouble with this is if you have to import files created by external systems since sometimes they "space" fill fields to a fixed length.