dcsimg
Results 1 to 5 of 5

Thread: [Access] Run-time error '3144': - Syntax error in UPDATE statement

  1. #1

    Thread Starter
    Member
    Join Date
    Oct 2010
    Posts
    56

    [Access] Run-time error '3144': - Syntax error in UPDATE statement

    Hello, everyone! I'm running an update query programmatically, and I'm receiving a syntax error for some reason. I can't seem to figure out what's wrong. Here's my code:

    Code:
    Dim db As Database
                
    Set db = CurrentDb()
    
    db.Execute "UPDATE tblBins SET datRepairDate = null, datClaimDate = null, " & _
                "txtRepairTech = " & vbNullString & ", txtTypeandSerial = " & vbNullString & ", " & _
                "txtAsset = " & vbNullString & ", " & _
                "txtPart1andFRU = " & vbNullString & ", txtPart2andFRU = " & vbNullString & ", " & _
                "txtPart3andFRU = " & vbNullString & ", txtPart4andFRU = " & vbNullString & ", " & _
                "txtPart5andFRU = " & vbNullString & ", memoProblemandTroubleshooting = " & vbNullString & ", " & _
                "intHEAT = null, txtLenovoClaimID = " & vbNullString & ", txtStatus = " & vbNullString & ", " & _
                "txtTrackingNumber1 = " & vbNullString & ", txtShortClaimNumber1 = " & vbNullString & ", " & _
                "txtTrackingNumber2 = " & vbNullString & ", txtShortClaimNumber2 = " & vbNullString & ", " & _
                "txtTrackingNumber3 = " & vbNullString & ", txtShortClaimNumber3 = " & vbNullString & ", " & _
                "txtTrackingNumber4 = " & vbNullString & ", txtShortClaimNumber4 = " & vbNullString & ", " & _
                "txtTrackingNumber5 = " & vbNullString & ", txtShortClaimNumber5 = " & vbNullString & ", " & _
                "boolComplete = False " & _
                "WHERE ((([tblBins].binID)='" & Me.binID & "'));"
    Everything is a String except for intHEAT, datRepairDate, datClaimDate, and boolComplete. Those variables are of the data types Integer, Date, Date, and Boolean, respectively.

    I've tried a lot of solutions and suggestions online, but I'm not sure what the syntax error is. Does anyone have any suggestions? Thanks in advance for any help!

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,021

    Re: [Access] Run-time error '3144': - Syntax error in UPDATE statement

    The first thing to do is find out what SQL statement your code is creating - you have got a huge block of code there, and you can't easily tell what it will produce by just looking at it (and some parts you can't be sure about).

    For a good way to do that, see the article How can I find out why my SQL statement isn't working? from our Database Development FAQs/Tutorials (at the top of the Database Development forum)


    Once you have done that, you will have far less text to look at (and it will be far simpler), and there is a good chance that you will be able to work out what the problem is... if you can't, show us the SQL statement that is produced.

  3. #3

    Thread Starter
    Member
    Join Date
    Oct 2010
    Posts
    56

    Re: [Access] Run-time error '3144': - Syntax error in UPDATE statement

    Quote Originally Posted by si_the_geek View Post
    The first thing to do is find out what SQL statement your code is creating - you have got a huge block of code there, and you can't easily tell what it will produce by just looking at it (and some parts you can't be sure about).

    For a good way to do that, see the article How can I find out why my SQL statement isn't working? from our Database Development FAQs/Tutorials (at the top of the Database Development forum)
    Thanks for the advice! I have followed the troubleshooting steps, but unfortunately it simply isn't working and is not throwing any errors.

    Here's the query code:

    Code:
    UPDATE tblBins SET tblBins.datRepairDate = Null, tblBins.datClaimDate = Null, tblBins.txtRepairTech = "", tblBins.txtTypeandSerial = "", tblBins.txtAsset = "", tblBins.txtPart1andFRU = "", tblBins.txtPart2andFRU = "", tblBins.txtPart3andFRU = "", tblBins.txtPart4andFRU = "", tblBins.txtPart5andFRU = "", tblBins.memoProblemandTroubleshooting = "", tblBins.intHEAT = Null, tblBins.txtLenovoClaimID = "", tblBins.txtStatus = "", tblBins.txtTrackingNumber1 = "", tblBins.txtShortClaimNumber1 = "", tblBins.txtTrackingNumber2 = "", tblBins.txtShortClaimNumber2 = "", tblBins.txtTrackingNumber3 = "", tblBins.txtShortClaimNumber3 = "", tblBins.txtTrackingNumber4 = "", tblBins.txtShortClaimNumber4 = "", tblBins.txtTrackingNumber5 = "", tblBins.txtShortClaimNumber5 = "", tblBins.boolComplete = False
    WHERE (((tblBins.binID)=[Forms]![frmGetBin]![binID]));
    The query has proved to work in the past.

    Here's the query code, done programmatically:

    Code:
    Dim db As Database
                
    Set db = CurrentDb()
    
    db.Execute " UPDATE tblBins SET tblBins.datRepairDate = Null, tblBins.datClaimDate = Null, " & _
                "tblBins.txtRepairTech = " & Chr(34) & Chr(34) & ", tblBins.txtTypeandSerial = " & Chr(34) & Chr(34) & ", " & _
                "tblBins.txtAsset = " & Chr(34) & Chr(34) & ", " & _
                "tblBins.txtPart1andFRU = " & Chr(34) & Chr(34) & ", tblBins.txtPart2andFRU = " & Chr(34) & Chr(34) & ", " & _
                "tblBins.txtPart3andFRU = " & Chr(34) & Chr(34) & ", tblBins.txtPart4andFRU = " & Chr(34) & Chr(34) & ", " & _
                "tblBins.txtPart5andFRU = " & Chr(34) & Chr(34) & ", tblBins.memoProblemandTroubleshooting = " & Chr(34) & Chr(34) & ", " & _
                "tblBins.intHEAT = Null, tblBins.txtLenovoClaimID = " & Chr(34) & Chr(34) & ", tblBins.txtStatus = " & Chr(34) & Chr(34) & ", " & _
                "tblBins.txtTrackingNumber1 = " & Chr(34) & Chr(34) & ", tblBins.txtShortClaimNumber1 = " & Chr(34) & Chr(34) & ", " & _
                "tblBins.txtTrackingNumber2 = " & Chr(34) & Chr(34) & ", tblBins.txtShortClaimNumber2 = " & Chr(34) & Chr(34) & ", " & _
                "tblBins.txtTrackingNumber3 = " & Chr(34) & Chr(34) & ", tblBins.txtShortClaimNumber3 = " & Chr(34) & Chr(34) & ", " & _
                "tblBins.txtTrackingNumber4 = " & Chr(34) & Chr(34) & ", tblBins.txtShortClaimNumber4 = " & Chr(34) & Chr(34) & ", " & _
                "tblBins.txtTrackingNumber5 = " & Chr(34) & Chr(34) & ", tblBins.txtShortClaimNumber5 = " & Chr(34) & Chr(34) & ", " & _
                "tblBins.boolComplete = False " & _
                "WHERE (((tblBins.binID)=" & Me.binID & "));"
    The output of the query done programmatically seems to exactly match that of the original SQL query. I am clueless as to what is wrong now. Any help would be greatly appreciated!

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,021

    Re: [Access] Run-time error '3144': - Syntax error in UPDATE statement

    Your code has improved a bit, and it is good to see that you have fixed at least one problem (but rather than " & Chr(34) & Chr(34) & " it would be neater to use '' )

    However, I get the impression that the query you showed us is not what the code produces, mainly due to the Where clause - and it is very important to look at what the code actually produces rather than making educated guesses (especially when the code uses variables etc).


    On a side note, you do not need to use tblBins. before field names anywhere (because there is only one table), nor do you need brackets in the Where clause... that is just the odd style that the Access query creator uses.

  5. #5

    Thread Starter
    Member
    Join Date
    Oct 2010
    Posts
    56

    Re: [Access] Run-time error '3144': - Syntax error in UPDATE statement

    Quote Originally Posted by si_the_geek View Post
    Your code has improved a bit, and it is good to see that you have fixed at least one problem (but rather than " & Chr(34) & Chr(34) & " it would be neater to use '' )

    However, I get the impression that the query you showed us is not what the code produces, mainly due to the Where clause - and it is very important to look at what the code actually produces rather than making educated guesses (especially when the code uses variables etc).


    On a side note, you do not need to use tblBins. before field names anywhere (because there is only one table), nor do you need brackets in the Where clause... that is just the odd style that the Access query creator uses.
    Thanks for the reply! My query just sets all the variables to either Null or "", and I have tested the output, and it looks correct. It seems I have found the problem:

    I have a set of 36 bins, and this code clears an individual bin in my Access table. Now, when I take out the "WHERE" conditional statement, the code does in fact execute and clears out all bins except the one I am currently viewing. So apparently, the code works fine; it's just it cannot clear out the bin I am viewing. I have closed all recordsets accessing the table for the bins, so I'm not sure why this is the case.

    Either way, I have found a workaround for my issue and have resolved it. However, if I could get an explanation for my peculiar situation, that would be great. Thanks for all of the help!

Tags for this Thread

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width