IsNull not working?

[lorirobn]

Hi,

My IsNull statement is not working (or at least not how I would expect it to!). Maybe I misunderstand the use of the function.

My statement is:

If IsNull(strAllCriteria) Or strAllCriteria = " " Then
    strPunctuation = " "
Else
    strPunctuation = ";"
End If

StrAllCriteria = "" (isn't that null?). Yet processing skips the first statement and goes to the Else. To satisfy my curiosity, I added:
If strAllCriteria = " " Then
debug.Print "it is blank"
ElseIf strAllCriteria = "" Then
Debug.Print "it is null"
Else: Debug.Print "i do not know"
End If
It prints 'it is null'.
Why isn't the IsNull statement true, then?
What have I done wrong?

 

[MajP]

Common mistake, here is why

Important Use the IsNull function to determine whether an expression contains a Null value. Expressions that you might expect to evaluate to True under some circumstances, such as If Var = Null and If Var <> Null, are always False. This is because any expression containing a Null is itself Null and, therefore, False

One thing I learned on this site is the following which checks both an empty string and a null. Used more with controls.

if trim(strAllCriteria)&" " = "" then

 

[snyperx3]

Null and "" are different. I know it doesnt seem right, but they are. MajP's example is a good way to do it. I always just used almost exactly what you have:
If IsNull(strAllCriteria) Or strAllCriteria = "" Then
I just take out that space you had. It works, but MajP's way is better in my opinion.

-Pete

 

[easyit]

yes, a string can never be null

 

[snyperx3]

yes, a string CAN be null

-Pete

 

[MajP]

I always just used almost exactly what you have:
If IsNull(strAllCriteria) Or strAllCriteria = "" Then

You missed the point
IsNull(strAllCriteria)
is always false.

Therefore if in fact strAllCriteria is null
If IsNull(strAllCriteria) Or strAllCriteria
returns False not true.

 

[lorirobn]

Thanks for the explanation. I tried your Trim suggestion, but that didn't work, either. I have to admit, I still don't understand the logic of why the IsNull doesn't come back as true. But I stopped trying (for now), and got it to work without the IsNull statement. I use:

If strAllCriteria = "" Or strAllCriteria = " " Then
    fsetpunctuation = " "
Else
    fsetpunctuation = ";"
End If

and it is working fine now. Oh well!!!???????


thx anyway...Lori

 

[MajP]

Never mind, I am going out loud and stupid. You are using IsNull() which is correct. My mouth is moving faster than my brain. I was thinking If Var = Null. Never mind.. Oops.

 

[MajP]

And to complete my brain fart:

if trim(strAllCriteria & " ") = ""

So here is what I was thinking, but not saying.

myString = ""
? isNull(myString)
False

myString = null
? isNull(myString)
True

myString = " "
? isNull(myString)
False

myString = null
? myString = null
Null (which evaluates to false)

myString = null
?trim(myString & " ") = ""
True
(That is what I meant to say)

myString = " "
?trim(myString & " ") = ""
True

Sorry about the confusion. it is monday.

 

[snyperx3]

My apologies. The basis of my statement was on text boxes of forms, and text fields for tables. Thats what i use that check on. When I think of a textbox i think that it stores a string. Just my personal way of thinking. I was carrying this over. I use: If IsNull(txtBox.Value) or txtBox.Value = "" Then

-Pete

 

[stix4t2]

how about this

if( trim(nz( str,""))="")then

nz(str,default) will test for null and return the default or the str.

and a string cannot be null, only a variant can contain a null, strings will raise an error if you assign it a null.

 

[lorirobn]

Thanks for all the feedback. I think mainly I didn't realize that "" and Null are different. I am used to programming in COBOL, where I can view exactly what lies in my fields, and it is confusing to me to not be able to do so and rely on what Access presents to me.

Can one view data in hexadecimal format in Access?

thx for spelling out all the possibilities, MajP! I guess as Monday wears on, the brain gets more focused, hehehe.

lori

 

[snyperx3]

hexadecimal can be viewed. The only time that ive used it, i stored the value as a long, and when the time came to view it, i used Hex(lngValue).

-Pete

 

[easyit]

Snyper,

Please tell me when a string is null?

EasyIT

 

[snyperx3]

Please refer to the post by me in this thread on 27th Feb 06 @ 15:24.

-Pete

 

[stix4t2]

In a table, if you define a field as text, the field will be null until a value is added into it. The definition allows you to specify if the value can zero length. This allows you to "" into it.

defined strings cannot be null.
the str will have "" assigned to it and will not let you assign a null
dim str as string
str = "" 'ok to use
str = null 'raises error

defined variants can contain null or a string
dim str as variant or
dim str
str = null 'ok to use
str = "" 'ok to use

If a field is text in a table it may contain a null.
You may assign it to a variant.
You must be sure it's not null before assigning to a string.

dim str as string
str = nz(tbl.str, "") 'the nz function test for null and
returns the default if value is null.

Hope this helps

 

[PHV]

Just my 2 cents:
If Trim(str & "") = "" Then
MsgBox "str is either null, empty, ZLS or blank"
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lorirobn]

great...that covers all bases. thanks.