Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2002
    Posts
    168

    Unanswered: Null field value

    When a new record is created and a field does not have default value, the value of the field becomes NULL. If let's I enter "Me" and then I clear/delete the field, does the value become NULL again, Empty or zero-length string ?


    Thanks

  2. #2
    Join Date
    May 2002
    Location
    Atlanta, GA
    Posts
    117

    Wink

    Hey Milan,

    Just curious as to why you need to know? Are you checking to see if the values are null for error checking? I've learned that in this situation a field is considered null until you type something in it, then it becomes not necessarily null but more like "". I think that means zero length????

    With Forms you may have the following code to check a fields situation:

    Private Sub Command_Click()
    If IsNull(Me.SomeField) Then
    MsgBox "Cannot leave field blank"
    Me.SomeField.SetFocus
    Exit Sub
    ElseIf Me.SomeField = "" Then
    MsgBox "Cannot leave field blank"
    Me.SomeField.SetFocus
    Exit Sub
    Else
    Do Something......
    End If
    End Sub

    Does this help you??? I to would like to know the answer to your question if I'm wrong.

    Kal

  3. #3
    Join Date
    Feb 2002
    Posts
    2,232
    The answer is empty or zero length string. You could re-null the field, but you have to be explicit. Once you modify a field that is null it is no longer null unless you explictly null the field. NULL means absence of value.

  4. #4
    Join Date
    Apr 2002
    Posts
    168
    Thanks everyone.

    What I am trying to do is compare two tables and see whether their field values are different.

    For example :

    Table 1

    Customer ID : 001
    Customer Name : John

    Table 2

    Customer ID : 001
    Customer Name : Smith

    What I did was to have two tables as two recordsets and compare the values for each record (search for the same customer ID). So my code looks like :

    If table1.customername <> table2.customername then
    Msgbox("Different")
    end if


    However, I find inconsistency when comparing null values. So, I need to use IsNull. I don't care whether the value is NULL, empty or zero-length string since all of them to users mean no value.

    If IsNull(table1.customername) Or IsNull(table2.customername) Then
    If IsNull(table1.customername) And IsNull(table2.customername) Then
    Else
    Msgbox(different)
    End If
    ElseIf table1.customername <> table2.customername Then
    Msgbox("Different")
    End If


    I simplify the table1 and table2.customername, it should be rst1 and rst2, for easy explanation purpose.

    So, that's why I am asking what actually happens when a user delete the "John" value. Will it come to NULL or any other value ? I try several operations, and it seems my code works fine, but I need to try more I guess. Please give me feedback on this code. Thanks.

  5. #5
    Join Date
    Apr 2002
    Posts
    168
    NB :

    I just tried deleting the "John" value, and when I checked the IsNull, it gives me True. So, basically, when we delete a value from a field, it becomes Null again, not empty or zero-length string ?

  6. #6
    Join Date
    Feb 2002
    Posts
    2,232
    How are you deleting this field ?

  7. #7
    Join Date
    Apr 2002
    Posts
    168
    Just highlight the value in the textbox and hit delete on the keyboard, OR backspace all the way.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •