Check if field is empty (what code to use)

  • Follow


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
 

0
Reply Lars 2/27/2010 4:46:51 PM

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/Forums.aspx/access/201002/1

0
Reply KenSheridan 2/27/2010 7:01:20 PM


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/Forums.aspx/access/201002/1
> 
0
Reply Lars 2/27/2010 7:58:32 PM

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/Forums.aspx/access/201002/1

0
Reply KenSheridan 2/27/2010 9:49:18 PM

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/Forums.aspx/access/201002/1
> 
0
Reply Lars 2/27/2010 10:06:35 PM

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
> 
> 
0
Reply John 2/27/2010 10:43:43 PM

Thanks for your comments.

Lars

"John Spencer" <spencer@chpdm.edu> schreef in bericht 
news:uLAuL5$tKHA.6140@TK2MSFTNGP05.phx.gbl...
> 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
>>
>> 
0
Reply Lars 2/28/2010 8:41:27 AM

oom�m��

"Lars Brownies" <Lars@Browniew.com> a �crit dans le message de groupe de 
discussion : hmda7k$j85$1@textnews.wanadoo.nl...
> Thanks for your comments.
>
> Lars
>
> "John Spencer" <spencer@chpdm.edu> schreef in bericht 
> news:uLAuL5$tKHA.6140@TK2MSFTNGP05.phx.gbl...
>> 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
>>>
>>> 
0
Reply joelgeraldine 3/17/2010 1:33:24 PM

7 Replies
3291 Views

(page loaded in 0.114 seconds)


Reply: