Missing ' - SQL syntax issue in VBA

Question

I am trying to run SQL statment with variable in VB Access. I get error for syntax issue. I think I am missing ' or " in the statement, but I am not sure what to do.

Here is the code.

If (rs.RecordCount <> 0) Then 
   rs.MoveFirst


Do While rs.EOF = False
TempExportCount = TempExportCount + 1
Checkdatacsv = rs![FileName68]
OriginalFileName = rs![FileName]

'***
'This is the variable - batch number
'*** 

BatchNO = rs![BatchNumber] 

FullPath = DirectoryPath & Checkdatacsv




DoCmd.TransferText acImportDelim, "AP-csv-Import", "tblAP_Payable_2", FullPath, False

Dim SQL2 As String

'***
'This is the statement
'*** 

SQL2 = "UPDATE tblAP_Payable_2 SET tblAP_Payable_2.BNO = " & BatchNO & "WHERE tblAP_Payable_2.BNO = 0;"


DoCmd.RunSQL SQL2

DoCmd.OpenQuery "qDEL_AP_BLank", acViewNormal, acEdit

When each line from csv files are imported, BatchNO is populated in the table so I will know each record is imported from which file

From what you think could be that answer, I suspect that there is an apostrophe somewhere in the data so I recommend that you use SQL parameters for the values instead of putting them directly into the string with &. Please see [How do I use parameters in VBA in the different contexts in Microsoft Access?](https://stackoverflow.com/q/49509615/1115360) — Andrew Morton, Sep 17 '19 at 18:21

score 0 · Answer 1 · answered Sep 17 '19 at 18:17

0

I think I found the answer :)

SQL2 = "UPDATE tblAP_Payable_2 SET tblAP_Payable_2.BNO = '" & BatchNO & "WHERE tblAP_Payable_2.BNO = 0;'"

answered Sep 17 '19 at 18:17

Ethan Namkung

33
7

This won't throw an error, but won't do what you want - it will overwrite the whole column. [How to debug dynamic SQL in VBA](http://stackoverflow.com/questions/418960/managing-and-debugging-sql-queries-in-ms-access/1099570#1099570) – Andre Sep 18 '19 at 08:11
This is missing the `'` after the BatchNo as shown in @Gustavs answer: `'" & BatchNO & "' WHERE` – Darren Bartrup-Cook Sep 18 '19 at 09:26

score 0 · Answer 2 · answered Sep 18 '19 at 07:09

0

Most likely, BatchNo is text, thus needs quotes and - as already noted - you miss a space:

SQL2 = "UPDATE tblAP_Payable_2 SET tblAP_Payable_2.BNO = '" & BatchNO & "' WHERE tblAP_Payable_2.BNO = 0;"

answered Sep 18 '19 at 07:09

Gustav

53,498
7
29
55

Missing ' - SQL syntax issue in VBA

2 Answers2