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
 

0
Lars
2/27/2010 4:46:51 PM
access 16762 articles. 2 followers. Follow

7 Replies
5476 Views

Similar Articles

[PageSpeed] 0

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
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
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
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
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
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
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
joelgeraldine
3/17/2010 1:33:24 PM
Reply:

Similar Artilces:

What's the EOL code for Editbox?
Dear all, I added a "Value" variable to a Editbox control in a MFC VC++ dialog, it's CString tx. The editbox has already been set MULTILINE=ENABLE and ACCEPT_RETURN=ENABLE. I try something like: tx.Format("%s\n%s",msg1,msg2); UpdateData(false); The two messages are still shown in the same line, no line- break is added in between. Could anyone tell me how I can add line-break to a Editbox- linked CString variable? Thank you very much, Raymond \r\n Sreeram Raymond wrote: > Dear all, > > I added a "Value" variable to a Editbox control in a MF...

merge MSN Bill Pay & Checking Acct
I want to merge my checking account with MSN Bill Pay but only see that I can merge MSN Bill Pay with my checking account (i.e., you lose all of your checking account information). Does any one have any suggestions? -- cheers, Tom G ...

Resource / Project level enterprise fields as dimensions in msp_project_timesheet
When I try to use Resource or Project level enterprise fields as dimensions in msp_project_timesheet, my actual billable hours always goes to zero. The actual billable hours reports correctly when I use the dimensions inherent to the timesheet. Is there a solution to this besides building my own virtual cube or writing a custom report against the reporting database? These were the two options I found in old posts. Was this problem fixed in a service pack that I missed? Thanks for your help. ...

Authorizing checks with RMS
I recently removed PCCharge and setup my syetm to work with a preferred merchant provider to allow native processing of credit & debit cards. Since check verification/authorization was handled by PCCharge to Telecheck, check authorization is now not being done. How is check authorization accomplished using a preferred provider? Thanks, Richard Unfortunately, it's not. PC Charge is the only option for check processing in RMS as far as I know. TPI or some other vendor may support it through an add-in, but RMS as shipped has no way of doing both checks and debit... -- -- Gle...

Check names not working for multiple address books / contacts
Hello! I have upgraded from outlook 2003 to outlook 2007, my contacts are stored in pst. I used to be able to search for a name via check names and it displayed me all the results from global address list and from my contacts, but now it only does one. It does not matter which one I set in the checking order, whichever is top will be the one that provides the result. I would like it to show me both results, how would I go about to set that up? Thank you Deana More accurate and complete information would be helpful. The Global Address List is only available to those who c...

Code for making Excel beep
Hi I'm not sure how to alter the code below to achieve what i want. I have the following code that i run at the end of a macro,which give an audible beep if conditions are met. If Range("B2").Value > Range("C5").Value And Range("C5") > "" Then xcount = 1 For xcount = 1 To 5 Beep newHour = Hour(Now()) newMinute = Minute(Now()) newSecond = Second(Now()) + 1 waitTime = TimeSerial(newHour, newMinute, newSecond) Application.Wait waitTime Next End If End Sub Below is part of a formula that outlines the new conditions that nee to be met. I...

How do I create Check list
1.I am new to excel and want to create a simple check list. I would like to have the first column check boxes and then the following columns discription and notes. Can someone give me a step by step on how to do this. I have created spread sheets but am not familiar with check boxes. 2.Also, Can someone tell me the difference between Access and Excel? Geek 1) Not sure what you're aiming to do with the checkboxes, but if you right click on the toolbar area, you will get a list of available toolbars. You want either the 'forms' toolbox or the 'controls' toolbox. Genera...

Check text field value against underlying table or query of combo box
Hello, I have a form on which the user can enter new records or update existing ones. If the user enters a new record on the form, I would like to have an 'After Update' event on one of the text fields, which is the item# to check if the value entered already exists in the underlying table, tbl_item. How can I call the table from the 'After Update' event of the text field and check for that value in the table? Thanks in advance. Regards, A. Crawford antcraw@swbell.net wrote: >I have a form on which the user can enter new records or update >existing ones. >If the us...

Using an html signature
My partner has started a new job and has been emailed an html file of the email signature she should use. Consulting emails from other employees of the company, I see that this consists of a logo and personal information at the TOP of the email, with a standard disclaimer appearing BELOW the body text. I've opened the html file in Explorer, copied the contents and pasted them into a new signature, but this places the signature below the insertion point. I can reposition the insertion point between the signature block and disclaimer, but this is fiddly and means for each new e...

Preventing multiple check boxes from being checked
I have a worksheet with over 20 rows of check boxes with 5 check boxes in each row. (from the forms toolbox) I would like to prevent the user from checking more than one box in each row. Is this possible? BTW, this forum has been a great help. I'm so glad that expert Excel users are so willing to share their wealth of knowledge. Thanks again. Jae Put them in a group box, again from the forms toolbar. Drag the group box onto the sheet, then drag the checkboxes in. -- HTH RP (remove nothere from the email address if mailing direct) "Jae" <Jae@discussions.micro...

Outlook keeps trying to send message from empty outbox
I was uploading a message with a file that I thought was below the max file size for my ISP and I got a message that the message was too big (6Meg). Now every time I send and recieve, Outlook tries to send a (the same?) message even when the outbox appears empty (the send receive details dialog shows one more message than is shown in the outbox) and then I get the same message after a few minutes. I have run scanpst and chkdsk repeatedly with no effect. Thanks!!!!! Create a new .pst file and make it the default. Then, go into your old .pst file and delete it from the outgoing folde...

Printing Checks
I'm having a problem printing checks from Money 2003. I have three accounts that come up in one .mny file. Two of the accounts use 3-on-a-page checks and I want the name and address printed on them. One account uses voucher checks and needs to be nudged a little south. When I go to print checks I have to be aware of what account I'm in and change the settings by hand to make sure the checks print correctly. How can I make the check-print settings stick with the account I'm using. It's a pain to have to remember every time. I'm frequently forgetting and screwing up a pag...

Check Names = "Exchange Server computer is busy..."
Here's what I got: Active Directory on Server 2003 1 Domain Controller also running DNS and WINS No other DNS or WINS servers (I know, I know) Static IP Addresses...NO DHCP Exchange Server 2003 on a member server Client running W2K SP4 Outlook 2000 SP3 Exchange has 4 mailboxes set, but nobody is using it. I'm still testing things out. When I try to connect to Exchange via my Outlook 2000, I get the following message: "The function cannot be performed because the Microsoft Exchange Server computer is busy. Try again later" I've tried it from 3 different client comp...

using bullets
Just trying to find out if I can or cannot do this in Publisher. There are only a few bullet characters available in publisher and I would like to have access to more. In word you simply got to format select bullets and then use the import button to add an unlimited number of bullets. It appears that you cannot do this with Publisher. I work much better with publisher than I do with Word. Any suggestions on how I can increase my bullet inventory. Thanks. -- A fledgling at 64 Hi Manny, I can only state this for Publisher 2007... Goto Format > Bullets and Numbering. C...

MultiValed Fields
I could be way off here, but I have over 60 fields (numeric fields that are scored 1-100) which represent observations of specific items that occur (are repeated) at different date-times for multiple subjects (SubID). The numeric fields are defaulted to be zero and I am having diffculty, for example, with selecting all fields >1 or >30 or >60 without querying them one at a time. Does it make sense for me to make a table and place all of these fields in a multi-valued field so I can then query the field for those criteria? Thanks No: don't use MVF for that. Whatever...

Envelope Address Font using Microsoft Offic Word 2003
Using the Tools Envelopes Label menu, I highlight the address of a letter and choose Size 10 envelope. On preview screen everything looks great, when I print out the address is in a huge font. I checked all the defaults and it is set to a 10 font. The return address prints fine. Any ideas Thank ...

554 error client host rejected wrong mail relay using Exchange 2003 /w SP2
I seem to be getting this error on a few domains that I'm sending mail to. We are using Windows 2003 /w Exchange 2003 SP2. Any idea's? Is this caused by a external DNS issue on the A or PTR record? Or... In external DNS we have our email server setup as mail2.abc.com but within Exchange we have our email server setup as mail1.abc.com would this be causing the issue? The following recipient(s) could not be reached: john@abc.com on 21/04/2006 9:54 AM There was a SMTP communication problem with the recipient's email server. Please contact your system ad...

Employee Check Format-Graphical
Does anyone know of a TK that shows how to convert one of the employee check from a text report to a graphical report? We do not have Direct deposit, I looked in the Report Downloads from Great Plains and did not see one there either. thanks -- Doug There's no secret here. Just un-check the "Text Report" option in the Report Definition window. And then modify the report so it looks like you want it. This will take some time. The reports don't transfer from text to graphical nicely. "Doug" wrote: > Does anyone know of a TK that shows how to convert...

Could very much use a Field List
I apologize if this is out there already, but it seems that at least a few field names changed from 1.2 to 1.3. Although I had downloaded a field list before, from here, I cannot even find that original post, let alone any more recent that might contain the correct field names. I need to do some custom SQL and would prefer not to reinvent the wheel. Thanks in advance very much to anyone who could help. Bud Izen Salem Oregon Do you have MS Access? Make a new project and attach to the database as your data source. You will be able to see all the tables and the field names. It has been ...

Printing MICR checks
Hello, We are having an issue printing checks on the Select Payables Checks window. When we try to print the checks we receive the error message below: "Warning: You have MICR but are not in a MICR screen. To use MICR, abort this screen and set up security access." The user has access to this screen but I'm not sure what this error message means. There was nothing related to this in the Knowledge Base either. Has anyone else received this error message before and if so how did/do you resolve it? Thanks! The user has the third party mekorma check writing module ...

Outlook insists sending a message even when outbox is empty
Receiving error message "Can not send ...(0x800CCC0D)" but the outbox is EMPTY! I have several email accounts and reading them all but sending out only thru one account. I suspect that there is an internal message trying to acknowledge that I have read a message. How can I delete it? kajtzu60 <kajtzu60@discussions.microsoft.com> wrote: > Receiving error message "Can not send ...(0x800CCC0D)" but the outbox > is EMPTY! I have several email accounts and reading them all but > sending out only thru one account. I suspect that there is an > internal messa...

Query on two tables with matching null fields
Suppose I have two tables: Table 1: ID a b c 1 1 2 2 3 4 3 5 6 4 7 8 9 Table2 ID a b c 1 1 2 2 3 4 3 5 6 4 7 8 9 The following SQL: SELECT Table1.ID, Table2.ID FROM Table1 INNER JOIN Table2 ON (Table1.c = Table2.c) AND (Table1.b = Table2.b) AND (Table1.a = Table2.a); Returns: Table1.ID Table2.ID 4 4 I do want these tables connected by inner joins not left or right (in other words I want only those records where all t...

How do i sort items using two rows per item in Excel?
Sorting single rows is simple, but is it possible to sort items that contain information in two rows so that both rows stay together? How? You will need a helper column that contains information to tie the two rows together in their proper relationship, such as Xxxxxx001, Xxxxxx002, etc etc with the "x"'s being your primary sort data...........then when you sort on that column things will be together. Vaya con Dios, Chuck, CABGx3 "primus4000" <primus4000@discussions.microsoft.com> wrote in message news:2BF7F60C-68C5-4778-94BA-2659480D40DB@microsoft.com... >...

Check off boxes
Publisher 2003 in use. I am creating a questionnaire that will be included in a newsletter. I am using the publisher software to make this form. Somewhere, in the past, I saw a keyboard shortcut to inset checkoff boxes, however, I cannot remember the key strokes. Any help along this line will be greatly appreciated. Thank you and enjoy your day. Mike Mike Reshetar wrote: > Somewhere, in the past, I saw a keyboard shortcut to inset checkoff boxes, > however, I cannot remember the key strokes. Use Wingdings and the � or q characters. -- Ed Bennett - MVP Microsoft Publisher h...

check check check
afpoiadshfpaosid It is a feature, not a bug -- Message posted from http://www.ExcelForum.com ...