If statement for Null numbers not catching them

[jcruzAME]

I have a piece of code that says:

If IsNull(Me.lstClientInvestments.Column(8, SelectedItem)) Then
EntityToPass = 0
Else

For some reason though, on entries that I KNOW the value is null and should run this code it doesn't. Then it skips to this line and I get a type mismatch error:

EntityToPass = Me.lstClientInvestments.Column(8, SelectedItem)

Any advice on why it won't run that code even if it IS a null value?

 

[harmankardon]

Hmm, you are sure its null and not a zero-length string?

That's the only thing I can think of.

 

[jcruzAME]

Yeah, it's an integer. Tried IsEmpty too and that doesn't catch it either.

 

[vbaInet]

Are you sure it's Null or zero-length string. Perform a zls check and if it is zls then it would be a good idea to turn off the Allow Zero-Length String property at table level.

However, the ultimate check goes like this:

If Len(Nz([Field]), vbNullString) <> 0 Then

I'm pretty slow with my reply then

 

[gemma-the-husky]

why 2 arguments within the column reference. what does that do?

 

[pbaldy]

why 2 arguments within the column reference. what does that do?

Column and row.

 

[vbaInet]

If Len(Nz([Field], vbNullString)) <> 0 Then

Amended a typo in the code

 

[gemma-the-husky]

ok

well, possibly in a list box, there ARE NO nulls. maybe any null is automatically converted to a ZLS. (as far as the listbox goes) I think that would make sense.

so as others have said, use nz, to test for both a null and a zls at the same time

 

[vbaInet]

The "real" value of the field the colum is bound to is preserved in a listbox or combo box. If it's Null the Column will be Null. There's no time that Access implicitly converts Null to zls. Like I mentioned previously, if the Allow Zero Length String property is turned off, you're sure that you will be dealing with just Nulls, nothing else.

 

[gemma-the-husky]

The "real" value of the field the colum is bound to is preserved in a listbox or combo box. If it's Null the Column will be Null. There's no time that Access implicitly converts Null to zls. Like I mentioned previously, if the Allow Zero Length String property is turned off, you're sure that you will be dealing with just Nulls, nothing else.

I just tried this, and I think you are wrong

I have just created a dbs, with a field in a table that is null, and isnull(dlookup(etc)) shows TRUE

in a list box, based on this table, the test isnull(column) etc evaluates as FALSE, so as far as the LIST BOX goes, the list box text is showing a zls.

I can't see a way to test this offhand, but I think a number in the list box is also TEXT (it is left-justified for one thing), but in most cases VBA automatically treats the text as a numeric - although (and I can't recollect the exact circumstance) I recollect cases were a specific comparison for a given numeric failed.


-----
[edit]
thinking about it, it would make sense is for a list box/combo box to display/manage a collection of strings - rather than try and manage a potentially complex collection of data. In which case there would not be any nulls.


here is an example. showme button on form1

View attachment db2.zip

 

[vbaInet]

Give another test. Remember to include the right index and row in the Column property. You're probably testing against the bound column.

 

[gemma-the-husky]

VBA - I edited post#10 to include a demo, probably while you were posting.

all I can say is I am repeating the observation raised by the OP. I can't think I have ever specifically tried to test a combobox/listbox column for null. I tend to use nz when testing pretty well anything.

 

[nanscombe]

A naughty shortcut that I have used for testing for an Empty (Zero Length) string or a NULL string is to concatenate an Empty string to it.

The value of a Non Zero length string isn't changed but a NULL string becomes a Zero length string. It changes an Integer / date to a string but since it's only a test what the hell.

IIf([Field] & "" = "", [true part], [false part])

No need for nz(), no need for IsNull.

And as it's only used as a test, not for returning values, it's worked fine for me.



Something that I have used to my advantage in the past is the fact that val(NULL & "") = 0.

So, re the OP I would be tempted to use..

EntityToPass = val(Me.lstClientInvestments.Column(8, SelectedItem)  & "")

I've successfully used this in several databases in the past.

 

[vbaInet]

VBA - I edited post#10 to include a demo, probably while you were posting.

all I can say is I am repeating the observation raised by the OP. I can't think I have ever specifically tried to test a combobox/listbox column for null. I tend to use nz when testing pretty well anything.

Well, that doesn't make sense, why doesn't it return Null when it's clearly Null? Your findings are right.

Now I've found that the problem lies with the Column property. It never returns Null, but the Value property does. Obviously the Value property returns the value of the Bound Column so a "normal" test for Null can only be done on the Bound Column using the Value property. Why was it developed like this? The return type of the Column property is Variant.