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

Null and evaluation statements

P: n/a
ken
Hi,
I was wondering when you have an empty cell, its value is null in the
debugger. So when you have an expression like this: "if len(mycell) < 1
then", or if you have "if mycell = null then" I never get the
evaluation statement to be true. Its always null. mycell = null in the
debugger, so why can't it understand if mycell = null then?

thanks

Nov 13 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
try if len(mycell) = 0 or if mycell = "" .. i've never looked into why
it doesent always equal null myself

Nov 13 '05 #2

P: n/a
ken
it does not work...the debugger says len(mycell) = null. but when I put
len(mycell) = null in the evaluation part of the if statement it does
not work. I should get true, but instead the whole expression evaluates
to null.

Nov 13 '05 #3

P: n/a
ken wrote:
Hi,
I was wondering when you have an empty cell, its value is null in the
debugger. So when you have an expression like this: "if len(mycell) <
1 then", or if you have "if mycell = null then" I never get the
evaluation statement to be true. Its always null. mycell = null in the
debugger, so why can't it understand if mycell = null then?

thanks


Nothing is ever "equal to" Null You have to use "Is Null" in queries or the
IsNull() function in VBA code and in ControlSource expressions.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Nov 13 '05 #4

P: n/a
ken
yep..that worked. isnull did.

Thankyou.

Nov 13 '05 #5

P: n/a
Ken
You might want to try using the IsNull() function.

Nov 13 '05 #6

P: n/a
Ken
You might want to try using the IsNull() function.

Nov 13 '05 #7

P: n/a
This is very true. Null means you don't know, so you can't
test against a value.

Another way around this is to use the Nz function. The first
parameter is the original expression. The second parameter
will be returned by the function if the first parameter is
Null.

Len(Nz(MyCell,'')) = 0

This will be true if a) MyCell is the zero-length string, or
b) MyCell is Null. If MyCell is not null, it will just
return it, allowing the Len() function to succeed.

Nz is the VBA function. Standard SQL includes a similar
function called Coalesce.

Kevin
Rick Brandt<ri*********@hotmail.com> 6/2/2005 12:32:49
PM >>>Nothing is ever "equal to" Null You have to use "Is Null"
in queries or the
IsNull() function in VBA code and in ControlSource
expressions. ken wrote:
Hi,
I was wondering when you have an empty cell, its value
isnull in the
debugger. So when you have an expression like this: "if

len(mycell) <
1 then", or if you have "if mycell = null then" I never

get the
evaluation statement to be true. Its always null. mycell

= null in the
debugger, so why can't it understand if mycell = null

then?

thanks

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Nov 13 '05 #8

P: n/a
"ken" <ge****@gmail.com> wrote in
news:11**********************@g14g2000cwa.googlegr oups.com:
I was wondering when you have an empty cell, its value is null in
the debugger. So when you have an expression like this: "if
len(mycell) < 1 then", or if you have "if mycell = null then" I
never get the evaluation statement to be true. Its always null.
mycell = null in the debugger, so why can't it understand if
mycell = null then?


Everything you ever needed to know about Nulls, explained very
nicely by a regular from this newsgroup:

http://allenbrowne.com/casu-12.html

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #9

P: n/a
ken wrote:
Hi,
I was wondering when you have an empty cell, its value is null in the
debugger. So when you have an expression like this: "if len(mycell) < 1
then", or if you have "if mycell = null then" I never get the
evaluation statement to be true. Its always null. mycell = null in the
debugger, so why can't it understand if mycell = null then?


The approach the Microsoft recommends as the best performer is to coerce the possible Null
value to a String value by concantenating an empty String (vbNullstring) to the Null and
then testing its length:

If Len(mycell & vbNullString) > 0 Then
' we have a non-Null value and non-empty String
Else
' we have a Null or Empty String ("" or vbNullString) value
End If

This would be an appropriate check if you are trying to determine if there is a text based
value as opposed to a Null or Empty String value.

--
'---------------
'John Mishefske
'---------------
Nov 13 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.