473,698 Members | 2,283 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How do I treat zero length the same as null

tdw
206 New Member
Hi all,

I've tried to find this answer in the help menu and online but haven't found it:

When I type in a field on my form, and then hit backspace to erase what I just typed, my understanding is that the field is now considered zero length, and not null. I have a field on the form that looks up info in another table based on what was just typed in that field (using an After Update event).

I tried, just in case, to use Nz, but as I expected that wasn't the trick.
Is there a way to get the After Update event code to ignore zero length in the same way Nz would ignore a null? Or to convert the zero length to a null and then use Nz?

I've also already tried changing the settings in the field on the table to not allow zero length, but that didn't do it either.

Here's the relevant part of the code for the After Update event on the form:
Expand|Select|Wrap|Line Numbers
  1. Private Sub ORDER_AfterUpdate()
  2.  
  3. '   Check to see if this client is already in the customers table
  4.  
  5.     If DCount("*", "cust", "[cusname]='" & Me!ORDER & "'") = 0 Then
  6.  
  7.     '   Yes No Box
  8.         Dim Msg, Style, Title, Response
  9.         Msg = "This Customer Name is not in the list. Do you want to add it? (Note: Don't add one-time customers, just clients that we expect to be recurring, such as title companies. If you think this client should already be in the list, check your spelling.)"    ' Define message.
  10.         Style = vbYesNo   ' Define buttons.
  11.         Title = "Add Customer"    ' Define title.
  12.         Response = MsgBox(Msg, Style, Title)
  13.             If Response = vbYes Then    ' User chose Yes
  14.                 DoCmd.RunMacro "Open Client Information Form"
  15.             End If
  16.  
May 29 '08 #1
7 3565
FishVal
2,653 Recognized Expert Specialist
....
When I type in a field on my form, and then hit backspace to erase what I just typed, my understanding is that the field is now considered zero length, and not null.....
Its not true. At least for basic Access controls like Textbox, Combobox etc.
If there is no text in control, then its Value property will return Null.

Expand|Select|Wrap|Line Numbers
  1. Private Sub ORDER_AfterUpdate()
  2.  
  3. '   Check to see if this client is already in the customers table
  4.  
  5.     If DCount("*", "cust", "[cusname]='" & Me!ORDER & "'") = 0 Then
  6.  
  7.     '   Yes No Box
  8.         Dim Msg, Style, Title, Response
  9.         Msg = "This Customer Name is not in the list. Do you want to add it? (Note: Don't add one-time customers, just clients that we expect to be recurring, such as title companies. If you think this client should already be in the list, check your spelling.)"    ' Define message.
  10.         Style = vbYesNo   ' Define buttons.
  11.         Title = "Add Customer"    ' Define title.
  12.         Response = MsgBox(Msg, Style, Title)
  13.             If Response = vbYes Then    ' User chose Yes
  14.                 DoCmd.RunMacro "Open Client Information Form"
  15.             End If
  16.  
Before you check whether a value exists in a table, check whether it is Null. If so, then the rest code should not be executed.

Regards,
Fish
May 29 '08 #2
missinglinq
3,532 Recognized Expert Specialist
When I type in a field on my form, and then hit backspace to erase what I just typed, my understanding is that the field is now considered zero length, and not null.
In point of fact, although this statement is made from time to time, testing shows this to be untrue! You can prove this for yourself very simply, using a command button with one line of code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub TestButton_Click()
  2.  If IsNull(Me.YourFieldName) Then MsgBox "Field Is Null"
  3. End Sub
  4.  
Now, enter data in your field then backspace to remove it or hilitr it then delight. Click the button and the messagebox will appear, telling you that the field is, indeed, Null. Entering only spaces in the field will yield the same result. I believe, and maybe someone else here knows differently and can speak up, the only time you can actually have a zero-length string in Access is if you import data from a non-Access program, when that program does allow zero-length strings.

Linq ;0)>
May 29 '08 #3
tdw
206 New Member
In point of fact, although this statement is made from time to time, testing shows this to be untrue! You can prove this for yourself very simply, using a command button with one line of code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub TestButton_Click()
  2.  If IsNull(Me.YourFieldName) Then MsgBox "Field Is Null"
  3. End Sub
  4.  
Now, enter data in your field then backspace to remove it or hilitr it then delight. Click the button and the messagebox will appear, telling you that the field is, indeed, Null. Entering only spaces in the field will yield the same result. I believe, and maybe someone else here knows differently and can speak up, the only time you can actually have a zero-length string in Access is if you import data from a non-Access program, when that program does allow zero-length strings.

Linq ;0)>
Based on both of your responses then using a Nz should work. I'll try it again.
As far as making a zero length string, if I understood what I was reading in the help file, I think that you can also type "" in the text box to create a zero length string.
May 29 '08 #4
missinglinq
3,532 Recognized Expert Specialist
I suppose it would, although I can't for the life of me imagine why anyone would do that! But as a poster for a local printing company used to say "The problem with making something idiot-proof is that idiots are so darn ingenious!"

Linq ;0)>
May 29 '08 #5
tdw
206 New Member
I suppose it would, although I can't for the life of me imagine why anyone would do that! But as a poster for a local printing company used to say "The problem with making something idiot-proof is that idiots are so darn ingenious!"

Linq ;0)>
The theory (in the help files) is that a "null" value means that there is no information for that field, and that a "zero length string" would mean there is info that should be there, but is unknown. I suppose maybe it can be useful for reports or something? I can't really picture how though.
May 29 '08 #6
Billiska
13 New Member
As far as making a zero length string, if I understood what I was reading in the help file, I think that you can also type "" in the text box to create a zero length string.
Hi, I have done some experiment to make this clear.
It is showed that: when you put "" in a textbox

If the field is set to allow zero length,
there will be no error message and the value received programatically is Null

However, if the field is set not to allow zero length,
there will be a non-user-friendly error message and the value is rejected(inside the program the value of the field will still be the last value before that)

What I think is confusing is that when you allow zero length string, you can put "" inside the text box but you will get Null as a result...
Sep 1 '08 #7
ADezii
8,834 Recognized Expert Expert
What I think is confusing is that when you allow zero length string, you can put "" inside the text box but you will get Null as a result...
If you Allow Zero Length Strings in a Text Field, namely (""), then move off the Field, the Value in the Field will 'not' equate to Null and the IsNull(Me![<Allow Zero Length String Field>]) Expression will equate to False.

Here are a few tidbits of useless information:
  1. A Null indicates an unknown Value in contrast to a Zero Length String which indicates that the value of a Field is known but the Field is empty. An analogy would be the storing of Fax Numbers in a Database. A Null indicates that you do not know whether or not a Customer has a Fax Number, whereas a Zero Length String indicates that a Customer has no Fax Number. In a Survey Database for color preferences regarding cars, a Zero Length String would be appropriate in the Color Preference Field if the User had "no preference", and now you can include this Response in Totals and Averages if you so desire.
  2. You must set the Allow Zero Length Property to allow Users to enter Zero Length Strings. If you do not set this Property and a User enters a Zero Length String, or all Blanks, Access will convert it to a Null before storing the value.
  3. You can join two Tables on Zero Length Strings, and two Zero Length Strings will compare to equal.
  4. A Null Value cannot be equal to any other Value, not even another Null.
  5. You cannot join two Tables on Null Values.
  6. Null Values do not participate in Aggregate Calculations such as: Sum() or Avg().
  7. Now that I have thoroughly confused everyone, I'll say goodbye! (LOL).
Sep 1 '08 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

6
3656
by: Frank | last post by:
I spent over an hour on the Internet & MSDN looking for an answer that worked for this problem. Lots of answers, none work. It's time to post a question. Using: VB6, ADO, MS Jet 4.0 How do you retrieve records (lines, rows...whatever) using SQL, searching for a specific field containing a "zero length string". I have tried every combination of quotes (single and double) and "IsEmpty". Many syntax combinations did not return an error,...
53
8178
by: Zhiqiang Ye | last post by:
Hi, All I am reading FAQ of this group. I have a question about this: http://www.eskimo.com/~scs/C-faq/q7.31.html It says: " p = malloc(m * n); memset(p, 0, m * n); The zero fill is all-bits-zero, and does not therefore guarantee useful null pointer values (see section 5 of this list) or floating-point zero values.
4
2475
by: Randall Parker | last post by:
I am designing a database schema. It happens to be in MySQL but I'm trying to keep it portable to other databases should the project grow. Anyway, suppose you have VARCHAR fields and will be using ASP.Net and ADO.Net. In your experience does it make more sense to allow NULL values for VARCHAR fields or will they behave well and get set to zero length strings when a user doesn't fill in a text field? I'm wondering if I should do:
1
1051
by: Randall Parker | last post by:
In places where ASP.Net does the handling of controls for you does it treat controls as null or as zero length strings?
10
4584
by: Lyle Fairfield | last post by:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaac11/html/acfctNZ_HV05186465.asp "If the value of the variant argument is Null, the Nz function returns the number zero or a zero-length string (always returns a zero-length string when used in a query expression)" **** How many records are there in FirstTable in which Product Is Null. SELECT COUNT(*) AS CountofNullProdcut
2
5308
by: zeljko.prince | last post by:
This is a copy from http://forums.mysql.com/read.php?10,73797,73797#msg-73797. Perhaps someone on this group will know the answer. Given the following table: CREATE TABLE foo(field VARCHAR(20) NOT NULL); The following query *can not* be executed: INSERT INTO foo VALUES(NULL);
22
4021
by: semedao | last post by:
Hi , I am using asyc sockets p2p connection between 2 clients. when I debug step by step the both sides , i'ts work ok. when I run it , in somepoint (same location in the code) when I want to receive 5 bytes buffer , I call the BeginReceive and then wait on AsyncWaitHandle.WaitOne() but it is signald imidiatly , and the next call to EndReceive return zero bytes length , also the buffer is empty. here is the code: public static byte...
3
11219
by: Coll | last post by:
I inherited a database. On one of the forms, a bunch of fields on the form are to be updated with data from a combo box containing many columns after one particular field is updated. I'm receiving an error message "Field cannot be a zero length string." I do have some records with null values for a few fields - no way around that. How can I prevent the error message from occurring? Here's a sample of the code... Me! = Me!.Column(9) Me!...
3
2181
by: angi35 | last post by:
I'm having a zero-length string problem... Hoping someone can help. (This is in Access 2000.) In FormA, I have a button that opens FormB with OpenArgs. In FormB, the OpenArgs are translated into default values for certain controls. FormB is a continuous form. For this reason, I can't assign .Value -- it has to be .DefaultValue. Otherwise it changes the values in the first record in the form. If any of the transferred controls in...
0
8676
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8608
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9164
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9029
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8870
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6524
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5860
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
3051
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2006
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.