Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2011
    Location
    Baltimore, MD
    Posts
    22

    Unanswered: Insert Null Value through UPDATE function

    Hi,

    I'm trying to do the following:
    Given certain criteria, I want to use the Update funtion to update a specific field in a field with a null value.

    Private Sub Readmitted_Click()
    Dim Q As VbMsgBoxResult
    Dim A As String, B As String, C As String
    Dim strSql As String

    A = "UPDATE tblCases SET tblCases.[Discharge Date] = NULL WHERE(((tblCases.ClientID)="
    B = ")"
    If Readmitted.Value = -1 Then
    Q = MsgBox("Fred", vbYesNo)
    If Q = vbYes Then
    strSql = A & Me.ClientID.Value & B


    I'm probably missing something really obvious. Help!!!

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    And what is your problem/error/question? This may help debug the SQL, which appears to have a parentheses problem:

    Debugging

    Further, you never actually execute the SQL.
    Paul

  3. #3
    Join Date
    Nov 2011
    Location
    Baltimore, MD
    Posts
    22
    The problem is that when I run the code, it is not recognizing NULL as a null value.

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You mean it's not setting the field to Null? What's your actual code? What you posted wouldn't compile if that's all there was.
    Paul

  5. #5
    Join Date
    Nov 2011
    Location
    Baltimore, MD
    Posts
    22
    My bad. It was the extra paren that I didn't notice. Thanks! I'm still a beginner at this.

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Happy to help. The query designer likes to put all those parentheses in. I always take the extraneous ones out when creating SQL in VBA (in your case, that's all of them). They're just too confusing otherwise.
    Paul

  7. #7
    Join Date
    Nov 2011
    Location
    Baltimore, MD
    Posts
    22
    One other question:
    If I wanted to make multiple fields to null (in the SET section), would I just rewrite the tblCases.[Discharge Date] = NULL like this:
    UPDATE tblCases SET tblCases.[Discharge Date] = NULL AND tblCases.[Date Referal]= NULL AND tblCases.[Active Start Date]= NULL AND tblCases.[Discharge Code]= NULL

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    No, "AND" would be appropriate in other places, but in the SET you separate multiple items with commas:

    UPDATE TableName
    SET Field1 = Whatever, Field2 = Something
    WHERE...
    Paul

Posting Permissions

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