Access Error 3141

Question

I am trying to set recordset for a report using the following query:

Dim RS As Recordset
Set RS = CurrentDb.OpenRecordset("Select DISTINCT SalesOrders.SalesOrderNumber, Vendors.Name, SalesOrders.OrderDate, SalesOrders.Grade, SalesOrders.QuantityUOM, SalesOrders.PortOfDischarge, SalesOrders.Quantity, IIf([SalesOrders.DTHCIncludedYN],'DTHC INCLUDED','DTHC NOT INCLUDED') AS DTHCIncludedYN," & _
" SalesOrders.DeliveryTerms, SalesOrders.SalesOrderID, SalesOrders.GenesisDocumenationAssistant, Products.ProductLoadPorts, Customers.CustomerType, SalesOrders.UnitPriceUOM, SalesOrders.UnitPrice, Customers.CustomerName, Products.ProductName, SalesOrders.PaymentTerms, SalesOrders.PlaceOfDelivery, SalesOrders.SalesCommission, SalesOrders.LatestShipDate, [SalesOrders.Quantity]*[UnitPrice] AS Amount," & _
" IIf([AdvisingBank]='GEB','GREAT EASTERN BANK',IIf([AdvisingBank]='BOC','BANK OF CHINA',IIf([AdvisingBank]='CB','CATHAY BANK',IIf([AdvisingBank]='HSBC','HSBC Bank USA',IIf([AdvisingBank]='COM','COMMERCE BANK'))))) AS [Bank Name]," & _
" IIf([CCICType]= '1','ONE ORIGINAL INSPECTION CERTIFICATE ISSUED BY CCIC NORTH AMERICA INC','ONE ORIGINAL PRESHIPMENT INSPECTION CERTIFICATE ISSUED BY ANY CCIC EUROPEAN OFFICE') AS [CCIC-Clause]," & _
" IIf([OnCarriageIncluded],'ON CARRIAGE INCLUDED','ON CARRIAGE NOT INCLUDED') AS OCIText, IIf(IsNull([PlaceOfDelivery]),[PortOfDischarge],[PlaceOfDelivery]) AS PODText, Vendors.AB1AddressLine1, Vendors.SupplierLocation AS [Swift Code], " & _
" IIf(IsNull(AdvisingBank),' ','TEL  ' & [Vendors.AB1Phone] & ', ' & 'FAX  ' & [Vendors.AB1Fax]) AS [Contact Details], IIf(IsNull(AdvisingBank),'',Vendors.AB1AddressLine1 & ', ' & [Vendors.AB1City] & ', ' & [Vendors.AB1State] & ' ' & [Vendors.AB1Zip] & ' ' & [Vendors.AB1Country]) AS AddressLine," & _
" FROM (Products INNER JOIN (Customers INNER JOIN SalesOrders ON Customers.CustomerID = SalesOrders.CustomerID) ON Products.Grade = SalesOrders.Grade) LEFT JOIN Vendors ON SalesOrders.AdvisingBank = Vendors.VendorID " & _
" WHERE (SalesOrders.SalesOrderNumber= Forms!frmPrintContracts!txtGreenSales  AND ((Customers.CustomerType)='GREEN' Or (Customers.CustomerType)='GREEN-JC' Or (Customers.CustomerType)='GREEN-DL' Or (Customers.CustomerType)='SIHU' Or (Customers.CustomerType)='PAPYRUS'))ORDER BY SalesOrders.SalesOrderNumber DESC ")

I am getting error saying that I have used reserved keyword or there is a punctuation mistake. Can anyone help me figure out what the error is. Any help is appreciated. Thank you

In such cases write the SQL into a variable upfront, `Debug.Print` it to the Immediate Window and copy paste it into a new query window. Then try to execute it. You'll get the same error message highlighting the piece in question. — paulroho, Jul 17 '17 at 15:33
You have an extra comma after `As AddressLine` - `As AddressLine, FROM (Products....`. You also don't have a space before `ORDER BY` - `='PAPYRUS'))ORDER BY` — Darren Bartrup-Cook, Jul 17 '17 at 15:43
Not an error, but you could shorten the SQL by replacing `((Customers.CustomerType)='GREEN' Or (Customers.CustomerType)='GREEN-JC' Or (Customers.CustomerType)='GREEN-DL' Or (Customers.CustomerType)='SIHU' Or (Customers.CustomerType)='PAPYRUS'))` **with** `Customers.CustomerType IN ('GREEN', 'GREEN-JC', 'GREEN-DL', 'SIHU', 'PAPYRUS')` https://www.w3schools.com/sql/sql_in.asp — Darren Bartrup-Cook, Jul 17 '17 at 15:48
[How to debug dynamic SQL in VBA](http://stackoverflow.com/a/1099570/3820271) — Andre, Jul 17 '17 at 16:06

Parfait · Answer 1 · 2017-07-19T03:12:07.870

Consider saving your SQL statement as an Access stored query and not VBA string for the following reasons:

All syntax errors are checked before you save. You cannot save via the MS Access query design GUI a non-compilable query.
Stored Access queries are more efficient than VBA string queries as the database engine saves the best execution plan for stored queries and cannot when called on the fly in VBA.

You can set a saved query to most Access objects (comboboxes or listboxes row sources, form or report recordsources) with less code.

Me.cboText.RowSource = "myStoredQuery"
Me.cboText.RowSourceType = "Table/Query"
Me.cboText.Requery

Me.Form.RecordSource = "myStoredQuery"
Me.Form.Requery

Your application code is more readable and maintainable as you avoid the VBA string concatenation. Plus, you abstract away the special-purpose nature of SQL from your application layer code.
```
Dim RS As Recordset
Set RS = CurrentDb.OpenRecordset("myStoredQuery")
```
The industry standard of parameterization is easier to achieve with stored queries which can serve as a prepared statement. If you ever need to pass VBA variable values for dynamic querying, you can parameterize stored queries with PARAMETERS clause and querydefs all while still using stored queries. See example below:

Stored Query
```
PARAMETERS [myParam] Date;
SELECT DISTINCT SalesOrders.SalesOrderNumber
FROM SalesOrders
WHERE SalesOrders.OrderDate = [myParam]
```
VBA
```
Dim qdef As QueryDef
Dim RS As Recordset

Set qdef = Currentdb.QueryDefs("myStoredQuery")
qdef!myParam = Date()

Set RS = qdef.OpenRecordset()
```

Access Error 3141

1 Answers1