If control is null then...

[Sam Y.]

Hello, I am trying to do a sort of validation of a control. The following is
basically what occurs when I click a button.

If [text18] = Null Then
MsgBox "textbox is null"
Else
MsgBox "textbox is not null"
End If

For some reason, even though the textbox contains no text and is seen when I
debug as null, the MessageBox ends up saying "textbox is not null", when in
fact, it has nothing in it. I'm assuming it's a syntax error but I can't
figure out how to check for null values.

 

[Rich K]

Check out the nz function, I bet it's what you want.

 

[Rich K]

oops, I mean the Isnull() function!

 

[George Nicholson]

If [text18] = Null Then
The above will always be False. You simply can't use equality (or other
comparisons) with Null. There isn't anything that is equal to Null, not even
Null. (Null = Null also returns False)

Use:
If IsNull([text19) Then

 

[Rich K]

Sorry, check out the isnull() function.

 

[Graham R Seach]

Sam,

Just to give you some background information...

Null is not a value; it is an 'undefined and indeterminate value'.
Therefore, it can't be used in a comparison expression. For example, the
expression (Null = Null) returns Null, not True or False.

To test if a value is undefined (Null), as Rich suggested, you must use the
IsNull function (which was specifically designed to test for a Null
condition). For example:
If IsNull(Null) Then MsgBox "I am Null"

Another way is to convert the Null to something that *can* be used in a
comparison expression. For example:
If Nz(Null, 0) = 0 Then MsgBox "I am Null"

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Sam Y.]

Now why's that method gotta be there! Oh well... just as I posted the entry,
I looked at the macro builder and saw the isnull() function. Thanks for your
reply!

Sorry, check out the isnull() function.

Hello, I am trying to do a sort of validation of a control. The following is
basically what occurs when I click a button.

If [text18] = Null Then
MsgBox "textbox is null"
Else
MsgBox "textbox is not null"
End If

For some reason, even though the textbox contains no text and is seen when I
debug as null, the MessageBox ends up saying "textbox is not null", when in
fact, it has nothing in it. I'm assuming it's a syntax error but I can't
figure out how to check for null values.

 

[Rich K]

No problem, glad you responded. I was beginning to think my posts were
invisible here.

Now why's that method gotta be there! Oh well... just as I posted the entry,
I looked at the macro builder and saw the isnull() function. Thanks for your
reply!

Sorry, check out the isnull() function.

Hello, I am trying to do a sort of validation of a control. The following is
basically what occurs when I click a button.

If [text18] = Null Then
MsgBox "textbox is null"
Else
MsgBox "textbox is not null"
End If

For some reason, even though the textbox contains no text and is seen when I
debug as null, the MessageBox ends up saying "textbox is not null", when in
fact, it has nothing in it. I'm assuming it's a syntax error but I can't
figure out how to check for null values.

 

[Klatuu]

I see you have your question answered, but I thought I would add a little
insight for future reference.

Null is a specail value that when compared to anything else will always
return False.
Even if you said ?Null = Null, it will return False. The IsNull function is
the only way to determine if a variable or field contains a Null Value.
There is another function that Rick touched on that is usefull when dealing
with Null values. It is the Nz() function. It evaluates the variable or
field in the first argument and if it is not null, it returns the value of
the variable. If it is Null, it returns the value specified in the second
argument. So:

X = Nz(FooBah, "Zinger")
If FooBah is Null, x will = "Zinger"; otherwise, it will equal the value of
FooBah

--
Dave Hargis, Microsoft Access MVP

Now why's that method gotta be there! Oh well... just as I posted the entry,
I looked at the macro builder and saw the isnull() function. Thanks for your
reply!

Sorry, check out the isnull() function.

Hello, I am trying to do a sort of validation of a control. The following is
basically what occurs when I click a button.

If [text18] = Null Then
MsgBox "textbox is null"
Else
MsgBox "textbox is not null"
End If

For some reason, even though the textbox contains no text and is seen when I
debug as null, the MessageBox ends up saying "textbox is not null", when in
fact, it has nothing in it. I'm assuming it's a syntax error but I can't
figure out how to check for null values.

 

[Sam Y.]

So basically you can't determine a control being empty unless you use the
IsNull() or Nz() methods? I was a bit confused because I used C# for some
school projects and control values can be compared by control.text = "",
however, this doesn't work in VBA I'm assuming. Thanks for all your input
everyone!
Sam

I see you have your question answered, but I thought I would add a little
insight for future reference.

Null is a specail value that when compared to anything else will always
return False.
Even if you said ?Null = Null, it will return False. The IsNull function is
the only way to determine if a variable or field contains a Null Value.
There is another function that Rick touched on that is usefull when dealing
with Null values. It is the Nz() function. It evaluates the variable or
field in the first argument and if it is not null, it returns the value of
the variable. If it is Null, it returns the value specified in the second
argument. So:

X = Nz(FooBah, "Zinger")
If FooBah is Null, x will = "Zinger"; otherwise, it will equal the value of
FooBah

--
Dave Hargis, Microsoft Access MVP

Now why's that method gotta be there! Oh well... just as I posted the entry,
I looked at the macro builder and saw the isnull() function. Thanks for your
reply!

Sorry, check out the isnull() function.

:

Hello, I am trying to do a sort of validation of a control. The following is
basically what occurs when I click a button.

If [text18] = Null Then
MsgBox "textbox is null"
Else
MsgBox "textbox is not null"
End If

For some reason, even though the textbox contains no text and is seen when I
debug as null, the MessageBox ends up saying "textbox is not null", when in
fact, it has nothing in it. I'm assuming it's a syntax error but I can't
figure out how to check for null values.

 

[Graham R Seach]

Sam,

Empty is different again. Use IsEmpty(var) for that.

No, I'm not kidding.

Nevertheless, I find the best way of determining whether a control contains
a valid value is to use the following construct. This of course assumes a
space is not a valid value:
If Len(Trim(Nz(Me!myTextbox, "")) = 0) Then 'it has value

If a space is a valid value, the just remove the Trim() function.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

So basically you can't determine a control being empty unless you use the
IsNull() or Nz() methods? I was a bit confused because I used C# for some
school projects and control values can be compared by control.text = "",
however, this doesn't work in VBA I'm assuming. Thanks for all your input
everyone!
Sam

I see you have your question answered, but I thought I would add a little
insight for future reference.

Null is a specail value that when compared to anything else will always
return False.
Even if you said ?Null = Null, it will return False. The IsNull function
is
the only way to determine if a variable or field contains a Null Value.
There is another function that Rick touched on that is usefull when
dealing
with Null values. It is the Nz() function. It evaluates the variable
or
field in the first argument and if it is not null, it returns the value
of
the variable. If it is Null, it returns the value specified in the
second
argument. So:

X = Nz(FooBah, "Zinger")
If FooBah is Null, x will = "Zinger"; otherwise, it will equal the value
of
FooBah

--
Dave Hargis, Microsoft Access MVP

Now why's that method gotta be there! Oh well... just as I posted the
entry,
I looked at the macro builder and saw the isnull() function. Thanks for
your
reply!

:

Sorry, check out the isnull() function.

:

Hello, I am trying to do a sort of validation of a control. The
following is
basically what occurs when I click a button.

If [text18] = Null Then
MsgBox "textbox is null"
Else
MsgBox "textbox is not null"
End If

For some reason, even though the textbox contains no text and is
seen when I
debug as null, the MessageBox ends up saying "textbox is not null",
when in
fact, it has nothing in it. I'm assuming it's a syntax error but I
can't
figure out how to check for null values.

 

[Rich K]

Sam,
Empty (or null) and a zero length string are two different things, so care
must be taken when making comparisons.

You CAN use the statement
If ControlName.text = "" then
' do some stuff
end if

But to check for either null or to a zero length string something like this
might be better
If Nz(Control.Text,"") = "" then
' do some stuff
end if

Rich

So basically you can't determine a control being empty unless you use the
IsNull() or Nz() methods? I was a bit confused because I used C# for some
school projects and control values can be compared by control.text = "",
however, this doesn't work in VBA I'm assuming. Thanks for all your input
everyone!
Sam

I see you have your question answered, but I thought I would add a little
insight for future reference.

Null is a specail value that when compared to anything else will always
return False.
Even if you said ?Null = Null, it will return False. The IsNull function is
the only way to determine if a variable or field contains a Null Value.
There is another function that Rick touched on that is usefull when dealing
with Null values. It is the Nz() function. It evaluates the variable or
field in the first argument and if it is not null, it returns the value of
the variable. If it is Null, it returns the value specified in the second
argument. So:

X = Nz(FooBah, "Zinger")
If FooBah is Null, x will = "Zinger"; otherwise, it will equal the value of
FooBah

--
Dave Hargis, Microsoft Access MVP

Now why's that method gotta be there! Oh well... just as I posted the entry,
I looked at the macro builder and saw the isnull() function. Thanks for your
reply!

:

Sorry, check out the isnull() function.

:

Hello, I am trying to do a sort of validation of a control. The following is
basically what occurs when I click a button.

If [text18] = Null Then
MsgBox "textbox is null"
Else
MsgBox "textbox is not null"
End If

For some reason, even though the textbox contains no text and is seen when I
debug as null, the MessageBox ends up saying "textbox is not null", when in
fact, it has nothing in it. I'm assuming it's a syntax error but I can't
figure out how to check for null values.

 

[Sam Y.]

So what's the difference between empty and null? And how'd you figure all
this stuff out, experience? Thanks for the thorough explanations, I posted
another thread about SQL statements, are you good at SQL queries as well?

Sam,

Empty is different again. Use IsEmpty(var) for that.

No, I'm not kidding.

Nevertheless, I find the best way of determining whether a control contains
a valid value is to use the following construct. This of course assumes a
space is not a valid value:
If Len(Trim(Nz(Me!myTextbox, "")) = 0) Then 'it has value

If a space is a valid value, the just remove the Trim() function.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

So basically you can't determine a control being empty unless you use the
IsNull() or Nz() methods? I was a bit confused because I used C# for some
school projects and control values can be compared by control.text = "",
however, this doesn't work in VBA I'm assuming. Thanks for all your input
everyone!
Sam

I see you have your question answered, but I thought I would add a little
insight for future reference.

Null is a specail value that when compared to anything else will always
return False.
Even if you said ?Null = Null, it will return False. The IsNull function
is
the only way to determine if a variable or field contains a Null Value.
There is another function that Rick touched on that is usefull when
dealing
with Null values. It is the Nz() function. It evaluates the variable
or
field in the first argument and if it is not null, it returns the value
of
the variable. If it is Null, it returns the value specified in the
second
argument. So:

X = Nz(FooBah, "Zinger")
If FooBah is Null, x will = "Zinger"; otherwise, it will equal the value
of
FooBah

--
Dave Hargis, Microsoft Access MVP


:

Now why's that method gotta be there! Oh well... just as I posted the
entry,
I looked at the macro builder and saw the isnull() function. Thanks for
your
reply!

:

Sorry, check out the isnull() function.

:

Hello, I am trying to do a sort of validation of a control. The
following is
basically what occurs when I click a button.

If [text18] = Null Then
MsgBox "textbox is null"
Else
MsgBox "textbox is not null"
End If

For some reason, even though the textbox contains no text and is
seen when I
debug as null, the MessageBox ends up saying "textbox is not null",
when in
fact, it has nothing in it. I'm assuming it's a syntax error but I
can't
figure out how to check for null values.

 

[Graham R Seach]

Sam,

Empty only refers to variant variables, and refers to an uninitialised
variant; one whose value has never been set. Empty variants have a VarType =
0.

Empty isn't the same as Null. Null is the state of having an
undefined/indeterminate value.

Variables containing zero-length strings ("") are neither Null or Empty. The
same can be said of numeric variables having a value = 0.
Experience, plus reading Help that came with the product.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

So what's the difference between empty and null? And how'd you figure all
this stuff out, experience? Thanks for the thorough explanations, I posted
another thread about SQL statements, are you good at SQL queries as well?

Sam,

Empty is different again. Use IsEmpty(var) for that.

No, I'm not kidding.

Nevertheless, I find the best way of determining whether a control
contains
a valid value is to use the following construct. This of course assumes a
space is not a valid value:
If Len(Trim(Nz(Me!myTextbox, "")) = 0) Then 'it has value

If a space is a valid value, the just remove the Trim() function.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

So basically you can't determine a control being empty unless you use
the
IsNull() or Nz() methods? I was a bit confused because I used C# for
some
school projects and control values can be compared by control.text =
"",
however, this doesn't work in VBA I'm assuming. Thanks for all your
input
everyone!
Sam

:

I see you have your question answered, but I thought I would add a
little
insight for future reference.

Null is a specail value that when compared to anything else will
always
return False.
Even if you said ?Null = Null, it will return False. The IsNull
function
is
the only way to determine if a variable or field contains a Null
Value.
There is another function that Rick touched on that is usefull when
dealing
with Null values. It is the Nz() function. It evaluates the
variable
or
field in the first argument and if it is not null, it returns the
value
of
the variable. If it is Null, it returns the value specified in the
second
argument. So:

X = Nz(FooBah, "Zinger")
If FooBah is Null, x will = "Zinger"; otherwise, it will equal the
value
of
FooBah

--
Dave Hargis, Microsoft Access MVP


:

Now why's that method gotta be there! Oh well... just as I posted
the
entry,
I looked at the macro builder and saw the isnull() function. Thanks
for
your
reply!

:

Sorry, check out the isnull() function.

:

Hello, I am trying to do a sort of validation of a control. The
following is
basically what occurs when I click a button.

If [text18] = Null Then
MsgBox "textbox is null"
Else
MsgBox "textbox is not null"
End If

For some reason, even though the textbox contains no text and is
seen when I
debug as null, the MessageBox ends up saying "textbox is not
null",
when in
fact, it has nothing in it. I'm assuming it's a syntax error but
I
can't
figure out how to check for null values.