Why do I get a SQL syntax error "Expected lexical element not found: =)"?

Question

Why do I keep getting a SQL syntax error:

Expected lexical element not found: =)

The following is my code: I can't seem to find where the syntax error is

Dim oConnection
Dim oRecordset
Dim sMsg
Dim sConnectString
Dim sSQL

customer = Forms.InvoiceForm.customerName
account = Forms.InvoiceForm.accountName
term = Forms.InvoiceForm.termName
names = Forms.InvoiceForm.nameText
addresses = Forms.InvoiceForm.addressText
city = Forms.InvoiceForm.cityText
state = Forms.InvoiceForm.stateText
postal = Forms.InvoiceForm.postalText
country = Forms.InvoiceForm.countryText
transDate = Forms.InvoiceForm.transactionText
dueDate = Forms.InvoiceForm.dueText
shipDate = Forms.InvoiceForm.shipText
tax = Forms.InvoiceForm.taxState
taxCode = Forms.InvoiceForm.taxCode

sConnectString = "DSN=Quickbooks Data;OLE DB Services=-2;"

sSQL = "INSERT INTO Invoice (CustomerRefListID, ARAccountRefListID,    TxnDate, RefNumber, BillAddressAddr1, BillAddressAddr2, BillAddressCity, BillAddressState, BillAddressPostalCode, BillAddressCountry, IsPending, TermsRefListID, DueDate, ShipDate, ItemSalesTaxRefListID, Memo, IsToBePrinted, CustomerSalesTaxCodeRefListID) VALUES ('" & customer & "', '" & account & "', #9/23/2020#, '1','" & names & "', '" & addresses & "', '" & city & "', '" & state & "', '" & postal & "', 'USA',0, '" & term & "', #10/31/2020# , #10/01/2020# , '" & tax & "','hello', 0,'" & taxCode & "')"

Set oConnection = CreateObject("ADODB.Connection")
Set oRecordset = CreateObject("ADODB.Recordset")
oConnection.Open sConnectString
oConnection.Execute (sSQL)
sMsg = sMsg & "Invoice was Sent to QuickBooks"
MsgBox sMsg

This hard coded version of the query worked no problem

INSERT INTO InvoiceLine (InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount, InvoiceLineSalesTaxCodeRefListID, FQSaveToCache) VALUES ('80002436-1519061496', 'Building permit 1', 1.00000, 1.00, '80000001-1478562826', 1)

INSERT INTO Invoice (CustomerRefListID, ARAccountRefListID, TxnDate, RefNumber, BillAddressAddr1, BillAddressAddr2, BillAddressCity, BillAddressState, BillAddressPostalCode, BillAddressCountry, IsPending, TermsRefListID, DueDate, ShipDate, ItemSalesTaxRefListID, Memo, IsToBePrinted, CustomerSalesTaxCodeRefListID) VALUES ('800001F6-1482536280', '8000001E-1478562986', #9/23/2020#, '1', 'Brad Lamb', '1921 Appleseed Lane', 'Bayshore', 'CA', '94326', 'USA', 0, '80000002-1478562832', #10/31/2020#, #10/01/2020#, '8000295C-1541711590', 'Memo Test', 0, '80000001-1478562826')

When running the debugger oConnection.Execute (sSQL) is being highlighted — , Jan 15 '19 at 16:53
I would suggest to output your SQL statement and verify it is valid. — Brandon Pratt, Jan 15 '19 at 17:09
The SQL statement is valid I have tested it, the issue started when I created a form in Access and assigned text boxes for the input — , Jan 15 '19 at 17:12
Insert `Option Explicit` as your very first line of code and compile the code. — Gustav, Jan 15 '19 at 17:46
Is that SQL statement still valid when all textbox values have been inserted? — Hans Keﬆing, Jan 15 '19 at 18:07
Can you use parameters in an Access SQL query? Then use that to prevent these issues — Hans Keﬆing, Jan 15 '19 at 18:10
Do any of the values in the textboxes contain an apostrophe? Even if they don;t, you should probably use parameters instead of concatenating strings (for various reasons) — D Stanley, Jan 15 '19 at 19:55
No they don't contain any apostrophes, I will change it to parameters after I see the query work with VBA — , Jan 15 '19 at 20:09
I did not have any of these issues when hard coding all of the values, so I know it is not the query itself. — , Jan 15 '19 at 20:11

score 0 · Answer 1 · answered Jan 15 '19 at 19:52

0

memo is a reserved word, you can't use it as a field name.

answered Jan 15 '19 at 19:52

Hopper

146
6

1

Yes, you can. Just escape the identifier with square brackets or backticks. – Parfait Jan 15 '19 at 19:59
Memo is a word used in QuickBooks, I have to use it or else the SQL query will not know where to put the value for "Memo" – Jan 15 '19 at 20:04
Is there a way I could maybe use it having a different word represent it? – Jan 15 '19 at 20:05
Parfait is correct, just encase the word memo in quotes or ticks. – Hopper Jan 15 '19 at 20:29
@RichardWalton ... Please re-run query with square brackets: `[Memo]` and report back. I am seeing you are re-asking this same topic question multiple times. – Parfait Jan 16 '19 at 16:28
I am not going to fill the memo field at all, deleted that entry all together, the problem has been found, it is the way date is being formatted. – Jan 16 '19 at 16:35
Please look at my newest post – Jan 16 '19 at 16:35

Why do I get a SQL syntax error "Expected lexical element not found: =)"?

1 Answers1