Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Setting a value to NULL (Access2000)

    This is messed up. I have a simple update query which may have all the values entered or it may have some null. If a value is null, I am trying to get the txtbox OR a variable to equal null, but I cannot get it to work. Can I not set a variable to NULL and have a NULL value get inserted in an update statement? This is wierd becase if I Physically put NULL in the update statement, it works fine, but why could I cannot set a variable to null instead?

    Thanks

    Sample
    SQLUpdate = " UPDATE Consultants " _
    & " SET FirstName = '" & Me.FirstName _
    & "', LastName = '" & Me.LastName _
    & "', Status = '" & Me.Status _
    & "' , StreetAddress = '" & CStr(Me.StreetAddress) _
    & "', City = '" & CStr(Me.City) _
    & "', Province = '" & Me.Province _
    & "', PostalCode = '" & Me.PostalCode _
    & "', PhoneNumber = " & Me.PhoneNumber _
    & ", PhoneNumber2 = " & Me.PhoneNumber2 _
    & ", FaxNumber = " & Me.FaxNumber _
    & ", Type = '" & Me.Type _
    & "', SignupDate = '" & Format(Me.SignupDate, "mm/dd/yyyy") _
    & "', EmailAddress = '" & Me.EmailAddress _
    & "', TotalPointsRewarded = " & Me.TotalPointsRewarded _
    & ", PointsOwed = " & Me.PointsOwed _
    & " WHERE ConsultantID = " & Me.ConsultantID

    DoCmd.RunSQL SQLUpdate

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Setting a value to NULL (Access2000)

    Let's take a closer look at what happens. We'll concentrate on a small bit of your SQL first:<pre>"UPDATE Consultants SET PhoneNumber = " & Me.PhoneNumber</pre>

    If Me.PhoneNumber contains 1234, this evaluates to<pre>"UPDATE Consultants SET PhoneNumber = 1234"</pre>

    which is OK, but if Me.PhoneNumber is empty (null), it evaluates to<pre>"UPDATE Consultants SET PhoneNumber = "</pre>

    which clearly is not valid, it should have been<pre>"UPDATE Consultants SET PhoneNumber = Null"</pre>

    So you have to break it up and check for Null at each step:<pre>SQLUpdate = "UPDATE Consultants SET FirstName = "
    If IsNull(Me.FirstName) Then
    SQLUPdate = SQLUpdate & "Null"
    Else
    SQLUpdate = SQLUpdate & "'" & Me.FirstName & "'"
    End If
    SQLUpdate = SQLUpdate & ", LastName = "
    If IsNull(Me.LastName) Then
    SQLUpdate = SQLUpdate & "Null"
    Else
    SQLUpdate = SQLUpdate & "'" & Me.LastName & "'"
    End If</pre>

    etc.

    Omit the single quotes for numeric/currency/boolean fields.

    Dates are a special case, you must surround them with # characters:<pre>SQLUPdate = SQLUpdate & ", SignupDate = "
    If IsNull(Me.SignupDate) Then
    SQLUpdate = SQLUpdate & "Null"
    else
    SQLUpdate = SQLUpdate & "#" & Format(Me.SignupDate, "mm/dd/yyyy") & "#"
    End If</pre>

    It's tedious, but necessary.

  3. #3
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting a value to NULL (Access2000)

    WOW, OK I will do that. Thanks for the input. I have a small sample code on the lost focus event which basically makes sure the phone number is in correctly or just sets that text box to null

    Thanks for your help, [img]/forums/images/smilies/smile.gif[/img]

Posting Permissions

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