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

[hydrakiller4000]

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:

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!

[si_the_geek]

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.

[hydrakiller4000]

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:

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:

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!

[si_the_geek]

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.

[hydrakiller4000]

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!