Not IsNull, IsNull

[kitty77]

I'm using the following...

If Not IsNull([customer]) then...
If [customer]<>"" then...

I'm assuming these do the same. But they seem to work sometimes, or one or the other.

If IsNull([customer]) then...
If [customer]=""

Same for these too.

Am I doing something wrong? Is there a better way?

Thanks...

 

[MajP]

Not wrong, but not complete.
Null is not the same as an empty string or a space. There are several ways to check for all three at once. Here is one

if not trim(me.customer & " ") = "" then

or you may see
if len(me.customer & "") > 0 then

 

[deletedT]

I use a function for this:

Function IsNothing(varToTest As Variant) As Boolean
    
    '  Tests for a "logical" nothing based on data type
    '  Empty and Null = Nothing
    '  Number = 0 is Nothing
    '  Zero length string is Nothing
    '  Date/Time is never Nothing

    IsNothing = True

    Select Case VarType(varToTest)
        Case vbEmpty
            Exit Function
        Case vbNull
            Exit Function
        Case vbBoolean
            If varToTest Then IsNothing = False
        Case vbByte, vbInteger, vbLong, vbSingle, vbDouble, vbCurrency, vbDecimal
            If varToTest <> 0 Then IsNothing = False
        Case vbDate
            IsNothing = False
        Case vbString
            If (Len(varToTest) <> 0 And varToTest <> " ") Then IsNothing = False
    End Select

End Function

Then I use it this way:

    IF IsNothing([Customer]) then
        ......
        ......
    End IF

 

[theDBguy]

I'm using the following...

If Not IsNull([customer]) then...
If [customer]<>"" then...

I'm assuming these do the same. But they seem to work sometimes, or one or the other.

If IsNull([customer]) then...
If [customer]=""

Same for these too.

Am I doing something wrong? Is there a better way?

Thanks...

Hi. When human eyes see a blank or empty field or textbox, we assume that field or control has nothing in it. However, the computer "sees" more than we can. There are at least three possible ways a computer might see what is inside that field/control: a Null or an Empty String (also known as a Zero Length String or ZLS) or a non-printable character (like a linefeed character or a tab character and others).


To check if a field/control is empty, we'll have to account for those three possibilities (remember, they all look alike to our eyes, but very distinct to the computer, who will be doing the evaluation).


So, to check for nulls, we'll have to use the IsNull() function. To check for ZLS, we can compare it against an empty string (""). And to check for a non-printable character, we can use the Len() or Asc() function. There are ways to combine all the above checks into one to make the code simpler, or you can explicitly use all of them to make sure nothing is missed.


Hope that makes sense...

 

[kitty77]

So, how would I combine these...

If Not IsNull([customer]) Or If [customer]<>"" then...

And this one too...

If IsNull([customer]) Or If [customer]=""

Would those work?

 

[theDBguy]

So, how would I combine these...

If Not IsNull([customer]) Or If [customer]<>"" then...

And this one too...

If IsNull([customer]) Or If [customer]=""

Would those work?

How about telling us in plain words (English) what you're trying to ask/check, so we can then translate/convert it into code for you? For example, you could say:


"If the [customer] field/control is empty (has nothing in it), then do something"


or you could say:


"If the [customer] field is NOT empty (has something in it), then do something"


or maybe something else along those lines.

 

[moke123]

If nz([customer],"") <>"" then...

If nz([customer],"") ="" then...

 

[theDBguy]

If nz([customer],"") <>"" then...

If nz([customer],"") ="" then...

What about non-printable characters, just in case?

 

[Deleted member 147267]

Whether or not you need to ever worry about that might depend on the situation and the character type but I can't think of a case where you'd need to worry about it. If you're thinking of RTF characters I don't think they are truly part of a string. Yes, you can inquire as to what

asc(left(var, 1)) is and get a number (##). However, if you check for the value of that character as in

instr(var, chr(##)) you should get 0 as a result.
If that is correct, IsNull or NZ on an RTF field that has been set to zls will disregard RTF characters and return Null.

DLookup on an empty string will return Null.

Also I do believe that if you remove an actual value from an RTF field (not referring to setting it to zls) any RTF characters will be deleted as well so the field will revert to Null.

What I don't know is what about other np characters such as Cr, Lf or other such line printing stuff.

 

[The_Doc_Man]

If the character is a member of a character set then it has non-zero length. This includes the ASCII "NUL" character - which of course is not NULL and (oddly enough) not a zero-length string either.

In general, when you want to perform this kind of test, you have at least a clue as to what you would expect if it is populated. That is, you know it is a string or an integer. So what you typically have to do in these cases is to pick the NZ variant that comes closest to your expected data type.

If NZ( variable, 0 ) = 0 then {do whatever you do for a null or zero answer}

If NZ( variable, "" ) = "" then {do whatever you do for a null or zero-length-string answer}

Note the special case mentioned earlier - that a sequence of blanks ALSO could occur. That would NOT be a zero-length-string case. This would mean that you would need to decide what a blank sequence means before deciding how to handle it programmatically.

I suppose it is possible to design one "special function" that covers all the cases and all the bases - but I tend to think of such things as a bit of overkill. If you have such a routine, it should be OK to use. But don't go writing something special when you can just pick the right form of NZ(x,v) to apply to your needs.

 

[MajP]

What is wrong with the solution I posted in the very first reply? It solves all cases

if not trim(me.customer & " ") = "" then

Space
null
zero length string
non printable
multiple spaces

 

[The_Doc_Man]

MajP, it does - but suffers from the issue of confusion if you were working with numbers. Perhaps it wasn't clear, but in the case of numbers, I believe it is easier to read and quickly comprehend the intent of NZ(x,0) for people who have to come back later to perform maintenance.

I have been bitten by the bug-a-boo of coming back to read my own un-cool code that I wrote in haste but regretted later because I had foolishly written something obscure and then didn't add a comment of WHY I made that choice. Which is why these days I pick the simplest code to CLEARLY accomplish my goals.

What you have works great, don't get me wrong. But my point is that sometimes you want to know more than "was this a ZLS?" - you might ALSO want separate confirmation that it was a blank string, particularly if you imported it from someplace and need to make the differentiation. My point was "know what you were trying to do so that you can know better what you ARE doing." Pick the code that fits the intent.

If that comes off as in ANY way insulting, it was NOT my intent.

 

[MajP]

If that comes off as in ANY way insulting, it was NOT my intent

In no way. My question was in the original post I provided a solution that combined checking for null, null string, and space and stated that. Then the OP came back with the question on how to combine the checks.
To check for a date use Isdate for a number use isnumeric