How do I treat zero length the same as null

[tdw]

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:

Private Sub ORDER_AfterUpdate()
       
'   Check to see if this client is already in the customers table

    If DCount("*", "cust", "[cusname]='" & Me!ORDER & "'") = 0 Then

    '   Yes No Box
        Dim Msg, Style, Title, Response
        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.
        Style = vbYesNo   ' Define buttons.
        Title = "Add Customer"    ' Define title.
        Response = MsgBox(Msg, Style, Title)
            If Response = vbYes Then    ' User chose Yes
                DoCmd.RunMacro "Open Client Information Form"
            End If

[FishVal]

....
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.

Private Sub ORDER_AfterUpdate()
       
'   Check to see if this client is already in the customers table

    If DCount("*", "cust", "[cusname]='" & Me!ORDER & "'") = 0 Then

    '   Yes No Box
        Dim Msg, Style, Title, Response
        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.
        Style = vbYesNo   ' Define buttons.
        Title = "Add Customer"    ' Define title.
        Response = MsgBox(Msg, Style, Title)
            If Response = vbYes Then    ' User chose Yes
                DoCmd.RunMacro "Open Client Information Form"
            End If

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

[missinglinq]

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:

Private Sub TestButton_Click()
 If IsNull(Me.YourFieldName) Then MsgBox "Field Is Null"
End Sub

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)>

[tdw]

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:

Private Sub TestButton_Click()
 If IsNull(Me.YourFieldName) Then MsgBox "Field Is Null"
End Sub

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.

[missinglinq]

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)>

[tdw]

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.

[Billiska]

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...

[ADezii]

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).