Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    update query (A2k3)

    Hi All,
    Just a simple little update query.....
    strUpdateSQL = "UPDATE tblMyTable "
    strUpdateSQL = strUpdateSQL & "SET [TestDate] = #" & rst!TestDate & "#, "
    strUpdateSQL = strUpdateSQL & "TextField1] = '" & rst![TextField1] & "', "
    strUpdateSQL = strUpdateSQL & "[NumField2] = " & rst![NumField2] & ", "
    strUpdateSQL = strUpdateSQL & "[ContactForeignKey] = " & rstNew![ContactPrimaryKey]
    strUpdateSQL = strUpdateSQL & " WHERE MyTableID = " & rst![MyTableID]

    Whenever the line above the WHERE clause gets updated to Null, I receive a syntax error in the UPDATE statement.
    I've tried using Nz(rstNew![ContactPrimaryKey], ""), I get the same error. If I try Nz(rstNew![ContactPrimaryKey], 0), then I get that the record can not be updated due to 'key violation'.

    So please, again, what am I forgetting? <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>
    Thank you.

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

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

    Re: update query (A2k3)

    Is it acceptable that ContactForeignKey would be set to Null (that is not the same as 0)?
    If it is a required field and/or a unique key field and/or part of a relationship with enforced relational integrity, this will not be the case.

  3. #3
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: update query (A2k3)

    Hi Hans,
    The field is not required, duplicates are accepted.
    It may be that the Contact has not yet been determined and added later via a form.
    Even when I do a Debug.Print and try to run that in a query I get the same error.

    btw, I knew that the 0 wouldn't work, I was just checking if I could get past that part of the statement even using a 'wrong' value. Sorry I didn't mention that.

    Thank you.

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

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

    Re: update query (A2k3)

    You'll have to replace

    strUpdateSQL = strUpdateSQL & "[ContactForeignKey] = " & rstNew![ContactPrimaryKey]

    by

    If IsNull(rstNew![ContactPrimaryKey]) Then
    strUpdateSQL = strUpdateSQL & "[ContactForeignKey] = Null"
    Else
    strUpdateSQL = strUpdateSQL & "[ContactForeignKey] = " & rstNew![ContactPrimaryKey]
    End If

  5. #5
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: update query (A2k3)

    Thanks Hans,
    Now that you've written that, I can now remember doing that same thing with other projects.
    I think I've got a memory leak.

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

Posting Permissions

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