Not IsNull, IsNull (1 Viewer)

K

kitty77

Registered User.
Local time
Yesterday, 20:09
Joined
May 27, 2019
Messages
710
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

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 20:09
Joined
May 21, 2018
Messages
8,525
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
 
D

deletedT

Guest
Local time
Today, 01:09
Joined
Feb 2, 2019
Messages
1,218
I use a function for this:

Code: 
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:
Code: 
    IF IsNothing([Customer]) then
        ......
        ......
    End IF
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 17:09
Joined
Oct 29, 2018
Messages
21,453
kitty77 said:
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...
Click to expand...
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...
 
K

kitty77

Registered User.
Local time
Yesterday, 20:09
Joined
May 27, 2019
Messages
710
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

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 17:09
Joined
Oct 29, 2018
Messages
21,453
kitty77 said:
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?
Click to expand...
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

moke123

AWF VIP
Local time
Yesterday, 20:09
Joined
Jan 11, 2013
Messages
3,911
If nz([customer],"") <>"" then...

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

Micron

AWF VIP
Local time
Yesterday, 20:09
Joined
Oct 20, 2018
Messages
3,478
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.
 
Last edited:
The_Doc_Man

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 19:09
Joined
Feb 28, 2001
Messages
27,138
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

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 20:09
Joined
May 21, 2018
Messages
8,525
What is wrong with the solution I posted in the very first reply? It solves all cases
if not trim(me.customer & " ") = "" then
Click to expand...
Space
null
zero length string
non printable
multiple spaces
 
The_Doc_Man

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 19:09
Joined
Feb 28, 2001
Messages
27,138
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

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 20:09
Joined
May 21, 2018
Messages
8,525
If that comes off as in ANY way insulting, it was NOT my intent
Click to expand...
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
 
You must log in or register to reply here.

Similar threads

K
If with . Attachement
Replies
11
Views
525
Cronk
Cronk
A
How i make remember password in form login on my pc only !
Replies
7
Views
522
KitaYama
K
oxicottin
Cant get form filter to work correctly
Replies
8
Views
197
plog
P
N
Chart Error: Expression is typed incorrectly, or is too complex to be evaluated
Replies
8
Views
439
Pat Hartman
P
R
Looking to Build a basic CRM database
Replies
11
Views
687
CJ_London
C

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom