Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2002
    Posts
    173

    Unanswered: if statement to find null field

    I'm trying to configure an if statement in Access97 to fill in a field if it's null. If something is already entered it will leave it alone. Here's an example of the code below: Any help would be greatly appreciated.

    Private Sub Emp_name_GotFocus()
    If Emp_name = null Then
    Emp_name = "Harvy"
    Else
    Emp_name = Emp_name
    End If
    End Sub

  2. #2
    Join Date
    Apr 2002
    Location
    Germany
    Posts
    228
    comparing with Null always results in Null (i.e. not true)

    either use the IsNull function: If IsNull(Emp_name)
    or even more comfortable the Nz function.

    Nz(Emp_name,"Harvy")

    will fill in Harvy if the field is null, otherwise it stays untouched.

  3. #3
    Join Date
    Jun 2002
    Posts
    173
    It worked great thanks.

    It brings up another very similar question. I have a field that has a default of "00000000-000". I'd like the field to return to a null value if it still equals the "00000000-000" when leaving the field. I tried using:

    Private Sub Co_RskNo_LostFocus()
    If [Co_RskNo] = "00000000-000" Then
    [Co_RskNo] = ""
    End If
    End Sub


    but I get a debug error on the [co_rskno]=""
    I'm sure my use of "" does not equal a null value here. Any ideas?

  4. #4
    Join Date
    Apr 2002
    Location
    Germany
    Posts
    228
    I'm assuming Co_RskNo is a formatted numeric field.
    So you just need to compare to (numeric) 0 and you cannot set it to Empty (only strings can) but to Null (as long as the field isn't defined as NOT NULL)

    Private Sub Co_RskNo_LostFocus()
    If [Co_RskNo] = 0 Then
    [Co_RskNo] = Null
    End If
    End Sub

  5. #5
    Join Date
    Jun 2002
    Posts
    173
    The co_rskno was actually a text field so I worked around it by setting up an additional text box, then used multiple if statements to update it if necessary. Thanks for all the help.

Posting Permissions

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