Check if field is empty (what code to use)

  • Thread starter Thread starter Lars Brownies
  • Start date Start date
L

Lars Brownies

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 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 said:
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 said:
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
Click to expand...
Click to expand...
 
Thanks again.

Lars

KenSheridan via AccessMonster.com said:
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 said:
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
Click to expand...
[quoted text clipped - 31 lines]
Lars
Click to expand...
Click to expand...
Click to expand...
 
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
 
Thanks for your comments.

Lars

John Spencer said:
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 said:
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
Click to expand...
Click to expand...
 
oomùmµù

Lars Brownies said:
Thanks for your comments.

Lars

John Spencer said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top