Check if field is empty (what code to use)

In a form's BeforeUpdate I check if certain fields are empty, and if that's
the case I cancel the BeforeUpdate. I always use this kind of code for that:

If IsNull([Surname] or Surname = "" then

However, I noticed that others use other ways for checking this:
If Len(Nz([Surname],"")) = 0 Then
If Len(Trim([Surname] & "")) = 0 then
If RTrim([Surname].Text) = Nothing Then
If Nz([SurName],"") = "" Then

I'm wondering what I can use best in this case.

Also I'm wondering, why do you have to check for a "" value? The only way a
textbox could get that value would be through vba, or am I missing
something?

Thanks,

Lars

Always take account of Murphy's Law. If a field or control can be Null or
contain a zero-length string, then, regardless of whether you think there is
no way a zero-length string can have been assigned to it, its prudent to test
for both, which your expression will do, as will the others, apart from
testing for Nothing, which refers to an unassigned object variable, so I'd
think would raise an error.

Ken Sheridan
Stafford, England

Lars Brownies wrote:
In a form's BeforeUpdate I check if certain fields are empty, and if that's
the case I cancel the BeforeUpdate. I always use this kind of code for that:

If IsNull([Surname] or Surname = "" then

However, I noticed that others use other ways for checking this:
If Len(Nz([Surname],"")) = 0 Then
If Len(Trim([Surname] & "")) = 0 then
If RTrim([Surname].Text) = Nothing Then
If Nz([SurName],"") = "" Then

I'm wondering what I can use best in this case.

Also I'm wondering, why do you have to check for a "" value? The only way a
textbox could get that value would be through vba, or am I missing
something?

Thanks,

Lars

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201002/1

Thanks Ken.
I've been doing this "" check only for text boxes bound to text fields. Are
you saying I should also do this for text boxes bound to for instance
numeric or date fields?

Lars


"KenSheridan via AccessMonster.com" u51882@uwe schreef in bericht
news:a44597143429b@uwe...
Always take account of Murphy's Law. If a field or control can be Null or
contain a zero-length string, then, regardless of whether you think there
is
no way a zero-length string can have been assigned to it, its prudent to
test
for both, which your expression will do, as will the others, apart from
testing for Nothing, which refers to an unassigned object variable, so I'd
think would raise an error.

Ken Sheridan
Stafford, England

Lars Brownies wrote:
In a form's BeforeUpdate I check if certain fields are empty, and if
that's
the case I cancel the BeforeUpdate. I always use this kind of code for
that:

If IsNull([Surname] or Surname = "" then

However, I noticed that others use other ways for checking this:
If Len(Nz([Surname],"")) = 0 Then
If Len(Trim([Surname] & "")) = 0 then
If RTrim([Surname].Text) = Nothing Then
If Nz([SurName],"") = "" Then

I'm wondering what I can use best in this case.

Also I'm wondering, why do you have to check for a "" value? The only way
a
textbox could get that value would be through vba, or am I missing
something?

Thanks,

Lars

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201002/1

No, only for fields of text data type. A date/time data type can only be a
true date/time value or Null; a field of number data type can only be a
number or Null; similarly with a currency data type, which is really just a
kind of number. With numbers or currency its always worth giving serious
consideration to whether the field should have a default value of zero or not.
With currency in particular I find that this is more often the case than not
due to the ambiguity of Null. As I've said here many times before, what does
a Null credit limit mean? No credit? Unlimited credit? There's no way of
knowing, its entirely a matter of interpretation. A zero credit limit is
unambiguous. Also you then don't have any problems with Nulls messing up
arithmetical calcaulations.

Ken Sheridan
Stafford, England

Lars Brownies wrote:
Thanks Ken.
I've been doing this "" check only for text boxes bound to text fields. Are
you saying I should also do this for text boxes bound to for instance
numeric or date fields?

Lars

Always take account of Murphy's Law. If a field or control can be Null or
contain a zero-length string, then, regardless of whether you think there
[quoted text clipped - 31 lines]

Lars

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201002/1

Thanks again.

Lars

"KenSheridan via AccessMonster.com" u51882@uwe schreef in bericht
news:a4470e594c461@uwe...
No, only for fields of text data type. A date/time data type can only be
a
true date/time value or Null; a field of number data type can only be a
number or Null; similarly with a currency data type, which is really just
a
kind of number. With numbers or currency its always worth giving serious
consideration to whether the field should have a default value of zero or
not.
With currency in particular I find that this is more often the case than
not
due to the ambiguity of Null. As I've said here many times before, what
does
a Null credit limit mean? No credit? Unlimited credit? There's no way
of
knowing, its entirely a matter of interpretation. A zero credit limit is
unambiguous. Also you then don't have any problems with Nulls messing up
arithmetical calcaulations.

Ken Sheridan
Stafford, England

Lars Brownies wrote:
Thanks Ken.
I've been doing this "" check only for text boxes bound to text fields.
Are
you saying I should also do this for text boxes bound to for instance
numeric or date fields?

Lars

Always take account of Murphy's Law. If a field or control can be Null
or
contain a zero-length string, then, regardless of whether you think
there
[quoted text clipped - 31 lines]

Lars

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201002/1

I tend to use
IF Len([object] & "") = 0 Then
...

Why? a control or a field can can be null or have a value. Text objects can
have a zero-length string value (especially if tied to a field that allows
zero Length strings.

If the object is a number, date, etc then it can be null or have a value, so
you could just test for null.

I don't know if this is still true, but at one time the check for length was
quicker than other tests. It probably makes very little difference in terms
of efficiency given the speed of modern computers. I would think you would
have to be in a loop with hundreds of thousands of iterations to see any
perceived difference as a human.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Lars Brownies wrote:
In a form's BeforeUpdate I check if certain fields are empty, and if
that's the case I cancel the BeforeUpdate. I always use this kind of
code for that:

If IsNull([Surname] or Surname = "" then

However, I noticed that others use other ways for checking this:
If Len(Nz([Surname],"")) = 0 Then
If Len(Trim([Surname] & "")) = 0 then
If RTrim([Surname].Text) = Nothing Then
If Nz([SurName],"") = "" Then

I'm wondering what I can use best in this case.

Also I'm wondering, why do you have to check for a "" value? The only
way a textbox could get that value would be through vba, or am I missing
something?

Thanks,

Lars

Thanks for your comments.

Lars

"John Spencer" schreef in bericht
...
I tend to use
IF Len([object] & "") = 0 Then
...

Why? a control or a field can can be null or have a value. Text objects
can have a zero-length string value (especially if tied to a field that
allows zero Length strings.

If the object is a number, date, etc then it can be null or have a value,
so you could just test for null.

I don't know if this is still true, but at one time the check for length
was quicker than other tests. It probably makes very little difference in
terms of efficiency given the speed of modern computers. I would think
you would have to be in a loop with hundreds of thousands of iterations to
see any perceived difference as a human.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Lars Brownies wrote:
In a form's BeforeUpdate I check if certain fields are empty, and if
that's the case I cancel the BeforeUpdate. I always use this kind of code
for that:

If IsNull([Surname] or Surname = "" then

However, I noticed that others use other ways for checking this:
If Len(Nz([Surname],"")) = 0 Then
If Len(Trim([Surname] & "")) = 0 then
If RTrim([Surname].Text) = Nothing Then
If Nz([SurName],"") = "" Then

I'm wondering what I can use best in this case.

Also I'm wondering, why do you have to check for a "" value? The only way
a textbox could get that value would be through vba, or am I missing
something?

Thanks,

Lars

oomùmµù

"Lars Brownies" a écrit dans le message de groupe de
discussion : ...
Thanks for your comments.

Lars

"John Spencer" schreef in bericht
...
I tend to use
IF Len([object] & "") = 0 Then
...

Why? a control or a field can can be null or have a value. Text objects
can have a zero-length string value (especially if tied to a field that
allows zero Length strings.

If the object is a number, date, etc then it can be null or have a value,
so you could just test for null.

I don't know if this is still true, but at one time the check for length
was quicker than other tests. It probably makes very little difference
in terms of efficiency given the speed of modern computers. I would
think you would have to be in a loop with hundreds of thousands of
iterations to see any perceived difference as a human.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Lars Brownies wrote:
In a form's BeforeUpdate I check if certain fields are empty, and if
that's the case I cancel the BeforeUpdate. I always use this kind of
code for that:

If IsNull([Surname] or Surname = "" then

However, I noticed that others use other ways for checking this:
If Len(Nz([Surname],"")) = 0 Then
If Len(Trim([Surname] & "")) = 0 then
If RTrim([Surname].Text) = Nothing Then
If Nz([SurName],"") = "" Then

I'm wondering what I can use best in this case.

Also I'm wondering, why do you have to check for a "" value? The only
way a textbox could get that value would be through vba, or am I missing
something?

Thanks,

Lars