IsNull vs IsEmpty? 6

[thatguy]

Hey there folks..

This is probably a remedial question, but what's the difference between IsEmpty, IsNull and (strvar = "&quot or(numvar = 0)? Could someone give an example? I've been playing around and it seems that as soon as my vars are Dim'd, they are automatically initialized to empty strings or zeros. When would you use which and why?

Thanks
-- michael~

 

[Fubear]

If I want to check to see ive a value is null or empty I tend to use

LEN(Nz(Value,"&quot)

that way you can check the length of the string and get an accurate result regardless of whether the contents are "", NULL, or a value.

 

[SgtJarrow]

IsNull means no data exists in the field.....Null can only be assigned to Variant Type fields.....unless NO DATA HAS EVER BEEN ENTERED INTO THAT FIELD. Then the field should still read NULL. Is Empty is a string that is Empty...not NULL.....strvar = "" just sets a string to empty.

NULL is typically used to signify the fact that the field is empty because you don't know the answer...

Empty (or "&quot is used to signify the field is empty because it is empty.....

For example, let's consider a Cell Phone Number....
You begin entering data for a new person, but you don't know if they have a cell phone...so the value is NULL. After talking to them, you find out that in fact they do not own a cell phone. The field should then be changed to Empty.

For the most part, Empty and NULL can be construed as the same thing....but in the instance above, you can see how they are distinctly different.

****************************
Only two things are infinite, the universe and human stupidity, and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III
MCSA, CNA, MCP, Network+, A+
w: robert.l.johnson.iii@citigroup.com
h: wildmage@tampabay.rr.com

 

[tcsbiz]

Excellent and concise answer Robert. Here's a star.

 

[tcsbiz]

Fubear,

Why would you not get an accurate result if you don't use
LEN(Nz(Value,"&quot)?

Thanks.
Tom.

 

[Rolliee]

null plus anything is null. null times anything is null. null just does not have any other rules to obey. The nz() changes a umerical variable to zero and a string to zero length or vacuous.

rollie e

 

[Fubear]

I actually use it to check on whether a field is blank or not.

I tried:
IF field.value =""
IF field.value IS NULL
IF LEN(field.value)>0 (result returned was NULL)
and other variants of the above to no effect. The debug tools in VB said the value in the field was NULL.

So I use that code to avoid any and all confusion, so I can be garunteed of geting a valid result no matter the data.

 

[tcsbiz]

Thanks Fubear. A star for you.

 

[BobJacksonNCI]

Since others may be reading this, I'll contribute to an already well-answered question.

Queries weren't mentioned...
If, in a query, you want to select if either of two fields contains a value, and one of them contains Null, you will NOT see the row in your results.
This happened to a fellow in my department who is learning Access. I explained Null to him this way:
(Speaking as if I'm Access)
Null means I don't know what the value is.
In a query, if I don't know the value (because of Null) of ANY of the fields, I disregard ALL of them.

Substituting "I don't know" for Null helped his comprehension.