Similar built-in functions for no data

[Guest]

It seems there are three similar function to deal with null or no data: IsNull, IsNothing, IsEmpty. I am confused and can't find info on these. What is the difference between them and when would you use one over the others
ctda

 

[Cheryl Fischer]

From VBA Help:

IsEmpty Function: Returns a Boolean value indicating whether a variable has
been initialized.

IsNull Function: Returns a Boolean value that indicates whether an
expression contains no valid data (Null).

There is no IsNothing function(). The Nothing keyword is used to
disassociate an object variable from an actual object. Use the Set statement
to assign Nothing to an object variable. For example: Set MyObject =
Nothing

If you will open Help while in a VB code window, go to Contents and click on
the topic: "Visual Basic Language Reference", you can see examples of how
to use each of these.

--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

It seems there are three similar function to deal with null or no data:

IsNull, IsNothing, IsEmpty. I am confused and can't find info on these.
What is the difference between them and when would you use one over the
others?

 

[Dirk Goldgar]

There is no IsNothing function().

One can however, test whether an object variable Is Nothing; e.g.,

If objMyObject Is Nothing Then
' do something
Else
' do something else
End If

 

[Douglas J. Steele]

PMFJI.

"no valid data", in the context of Null, just means that there isn't a known
value. It's the difference between not knowing what the value is, and
knowing that there isn't a value.

For example, you don't know my wife's maiden name. If you needed to capture
that information, you'd leave the field Null until you found out. Once you
found out, you'd either set it to a value (the maiden name), or to blank (if
I'm not married).

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Cheryl,

Thanks for answering.

Part of my problem is that I have never understood what "null" means. It

apparently doesn't mean "nothing" or "empty" or "no data", but rather "no
valid data" as you said. However, what the heck does "no valid data" mean?
And how does VB know whether or not data is valid?

 

[Guest]

1) So, does this mean that Null is synonymous with "no data has been entered or assigned as yet" ? And is that why when a user enters something in a field, then erases it again before leaving the field, the field is no longer Null

2) I have set a string variable to have a value of "" and yet VB says its Null, which seems to contradict what you said. I thought that a string set to "" was supposed to be different from Null

3) Also, is a zero-length string the same as ""

ctda

----- Douglas J. Steele wrote: ----

PMFJI

"no valid data", in the context of Null, just means that there isn't a know
value. It's the difference between not knowing what the value is, an
knowing that there isn't a value

For example, you don't know my wife's maiden name. If you needed to captur
that information, you'd leave the field Null until you found out. Once yo
found out, you'd either set it to a value (the maiden name), or to blank (i
I'm not married)

-
Doug Steele, Microsoft Access MV
http://I.Am/DougSteel
(No private e-mails, please

Cheryl

apparently doesn't mean "nothing" or "empty" or "no data", but rather "n
valid data" as you said. However, what the heck does "no valid data" mean
And how does VB know whether or not data is valid

 

[Dirk Goldgar]

1) So, does this mean that Null is synonymous with "no data has been
entered or assigned as yet" ? And is that why when a user enters
something in a field, then erases it again before leaving the field,
the field is no longer Null?

I think that can only happen with text fields that allow zero-length
strings as values. For other fields, if you enter something in the
field and then erase it, the value will be Null.

2) I have set a string variable to have a value of "" and yet VB says
its Null, which seems to contradict what you said. I thought that a
string set to "" was supposed to be different from Null.

It is impossible for a variable of type String to have a value of Null.
If you think VB is saying this, you must be misinterpreting your
results. Bear in mind that tests for Null can be confusing, because by
definition no value can be equal to Null -- not even a Null value. For
example, this code ...

Dim x As Variant

x = Null
If x = Null Then
MsgBox "Null"
Else
MsgBox "Not Null"
End If

.... will always display "Not Null".

3) Also, is a zero-length string the same as "" ?

Yes.

 

[Guest]

Dirk

Thanks much for your explanations. This helps a lot. You hit the nail on the head when you said "tests for Null can be confusing". I think I will do what I can to avoid tests for Null. If you always assign a default value to a field or control before the user does anything with it, then I believe it shouldn't be necessary to ever use a Null test

ctda

----- Dirk Goldgar wrote: ----

1) So, does this mean that Null is synonymous with "no data has bee
entered or assigned as yet" ? And is that why when a user enter
something in a field, then erases it again before leaving the field
the field is no longer Null

I think that can only happen with text fields that allow zero-lengt
strings as values. For other fields, if you enter something in th
field and then erase it, the value will be Null

2) I have set a string variable to have a value of "" and yet VB say
its Null, which seems to contradict what you said. I thought that
string set to "" was supposed to be different from Null

It is impossible for a variable of type String to have a value of Null
If you think VB is saying this, you must be misinterpreting you
results. Bear in mind that tests for Null can be confusing, because b
definition no value can be equal to Null -- not even a Null value. Fo
example, this code ..

Dim x As Varian

x = Nul
If x = Null The
MsgBox "Null
Els
MsgBox "Not Null
End I

.... will always display "Not Null"

3) Also, is a zero-length string the same as ""

Yes

 

[Dirk Goldgar]

Dirk,

Thanks much for your explanations. This helps a lot. You hit the
nail on the head when you said "tests for Null can be confusing". I
think I will do what I can to avoid tests for Null. If you always
assign a default value to a field or control before the user does
anything with it, then I believe it shouldn't be necessary to ever
use a Null test.

As an aside, in those cases where you want to check for whether a text
field is either blank or Null -- not caring which -- it's handy to
concatenate a zero-length string to the value and test to see if the
result is a zero-length string. For example,

If Len(Me.txtMyTextBox & vbNullString) = 0 Then
MsgBox "Hey, this field is empty!"
End If

 

[Tim Ferguson]

I think I will do what I can to avoid tests for Null. If you always
assign a default value to a field or control before the user does
anything with it, then I believe it shouldn't be necessary to ever use
a Null test.

You probably won't be able to get away with this, I'm afraid: and it's
partly Access's fault and it's partly Jet.

Most databases will allow text fields to have a value ("Eric") or a NULL,
but Jet muddies the water slightly with the AllowZeroLength property, which
allows the inbetween value "". There are certainly situations where this is
appropriate, but it does confuse matters.

Now the kicker. Access text boxes will RTRIM all values before returning
them, so that if you enter "Me ", you'll get back "Me". To make things
worse, it will convert "" to NULL, so that if you enter " ", you won't
get "" but NULL. There is nothing you can do about this behaviour[1], I'm
afraid. The advantage of this is that when you use numeric or date fields,
they get the legal value of NULL, whereas "" is not a valid date or number.

Compare this to Visual Basic, where an empty text box returns "", and this
value has to intercepted before it causes hundreds of Data Type errors if
it gets posted to a numeric or date field.

As I said above, however, Jet itself is quite happy with "" or " " in text
fields: you can check this with

UPDATE MyTable SET MyText = " " WHERE ID = 1023;

then

SELECT Len(MyText) FROM MyTable WHERE ID = 1023;

or

SELECT MyText IS NULL FROM MyTable WHERE ID = 1023;

and you get the right answers. It's just the Access forms getting in the
way.

The bottom lines are, then:

1) always use Variants to pass field values to and from;
2) check them with IsNull before assuming they hold a value;
3) take an _explicit_ decision about the meaning of 0, NULL and ""

[1] You can work round it, though, if you really want a "" in a text field:

Private Sub MyText_BeforeUpdate(Cancel as Integer)
' this is the control, use dot
If IsNull(Me.MyText.Value) Then
' this is the field, use bang
Me!MyText = ""
End If

End Sub


but only if you really, really, really NEED it!

Hope it helps


Tim F

 

[Dirk Goldgar]

Now the kicker. Access text boxes will RTRIM all values before
returning them, so that if you enter "Me ", you'll get back "Me".
To make things worse, it will convert "" to NULL, so that if you
enter " ", you won't get "" but NULL. There is nothing you can do
about this behaviour[1], I'm afraid.

Actually, there is a special trick for this that is supported by text
boxes. If you enter

""

in the control -- that is, enter the paired quotes that represent a
zero-length string -- they will be interpreted as a zero-length string
value, and that is what will be assigned to the control.

 

[Guest]

Thanks to everyone who contributed to this thread. I've learned a lot. I have a lot of new "food for thought" on testing for Nulls, etc. How a newby to VBA would ever find out all these nuances otherwise is beyond me. Reference books don't seem to ever include this kind of stuff, at least the one I have doesn't, and neither does the Access Help info
ctda

 

[Tim Ferguson]

If you enter

""

in the control -- that is, enter the paired quotes that represent a
zero-length string -- they will be interpreted as a zero-length string
value, and that is what will be assigned to the control.

Learn something every day...

I would not have stumbled across this in a million years: how did you find
out about it?

All the best


Tim F

 

[Dirk Goldgar]

Learn something every day...

I would not have stumbled across this in a million years: how did you
find out about it?

I really don't remember. I may have simply stumbled across it, but it's
in the Access 97 help, indexed under "zero-length strings, entering in
field", so most likely I learned it there. I mostly learned Access by
reading the help file.