Question Difference between Null

[sakthivels]

Respected people,

Can some one please advise me what is the difference between

Null, vbNullString, ""

The reason I ask is setting the property of text box at runtime ggave me some problems.

when I set txtName.text = "" I get an error message whereas when I try txtName= Null works.

Kindly advise on this issue.

Regards,
Sakthivel

 

[WIS]

Respected people,

Can some one please advise me what is the difference between

Null, vbNullString, ""

The reason I ask is setting the property of text box at runtime ggave me some problems.

when I set txtName.text = "" I get an error message whereas when I try txtName= Null works.

Kindly advise on this issue.

Regards,
Sakthivel

You must have "AllowZeroLengthString" ppty of the fld in the tbl set to "No" - this will disallow the "". Set it to "Yes" if you want to put "" in the ctrl (hence fld).

 

[Atomic Shrimp]

Null means no value is assigned to the variable/field - this often happens to be the state of text fields that have never been touched.

"" is an empty or zero length string, but it's not the same (even though it looks the same visually) - this is what typically ends up being the state of a text field where something is entered, then later deleted.

This means that sometimes you have to put: ="" OR Is Null as your criteria. Null is not 'like' anything, but neither is it 'unlike' anything, so sometimes you have to write criteria expressions such as:Not Like "Banana" AND Is Not Null.

 

[marianne]

you can also include what is the difference between "" and Empty ?

 

[Atomic Shrimp]

you can also include what is the difference between "" and Empty ?

Is there a difference, for strings?

 

[Galaxiom]

Null is certainly nothing but it is very important in databases and must never be overlooked. Something to watch for with Null is that it is not included in a not equal expression.

For example:
WHERE table.field <> 5 does not return the Null records even though in common sense, Null certainly is not equal to 5.

If you want the records where the field is not equal to 5 you need to use:
WHERE table.field <> 5 OR table.field Is Null

It can be a real trap, going unnoticed where there are a small number of nulls among many records.

 

[gemma-the-husky]

the basic problem with text fields is the inability to distinguish between null and a zero length string by inspection alone

although a number can be null, it will show as a blank, which is clearly different from a zero

note that a typed VARIABLE CANNOT be null (other than a variant)

-----------
now, there have been long discussions here about the usefullness of nulls, and you must decide whether you want them or not. You can easily prevent them by making your fields required=true

a zero length string (zls) is not null - it is simply a blank string - ie it contains "". You can disallow these by setting a field as allow zero length string = no

vbnullstring is just a constant that can be used in place of "". However it is useful in that it guarantees to work in any environment - lets say MS changed the way strings are implemented - eg, a pascal string is stored in memory differently to a c string, and comparing a string to "" may not work correctly in all cases - but its guaranteed to work if you use vbnullstring (that is my understanding, anyway)

you have to be careful how you set the allow zls, as users can easily edit a value to a blank (zls) and you get inadvertent run time errors if you dont allow a zls

---------
the nz function is very useful as it encapsulates BOTH null and zls strings - so you can say

if nz(myfield,vbnullstring) = vbnullstring then ... etc

or
if nz(myfield,0) = 0 (for a numeric field)

 

[sakthivels]

Thanks Dave for clarifiying me on the difference.