SQL in VBA Help 1

[3239]

No matter how much I try to correct the syntax of my VBA sql statements, I always get errors every time. Does anyone know where I can find tutorials explaining the syntax of sql statements in VBA? It's like hit or miss with me. Sometimes I get the syntax right. Most of the time I don't.

The erroneous code is below.

Thanks for your help.

docmd.RunSQL "SELECT Format([CSOrderID],"\A0000") AS Requisition, " & _
"Suppliers.SupplierName, Employees.EmployeeID," & _
"Employees.FirstName & " " & [LastName] AS Name," & _
"DepartmentInfo.Center, DepartmentInfo.Department, DepartmentInfo.Address, " & _
"DepartmentInfo.City, DepartmentInfo.State, DepartmentInfo.ZipCode," & _
"Suppliers.Address, Suppliers.City, Suppliers.PostalCode, Suppliers.StateOrProvince, " & _
"CS_REQ.OrderDate, CS_REQ.CSComments " & _
"FROM (Employees INNER JOIN DepartmentInfo ON Employees.EmployeeID = " & _
"DepartmentInfo.EmployeeID) " & _
"INNER JOIN (Suppliers INNER JOIN CS_REQ ON Suppliers.SupplierID = " & _
"CS_REQ.SupplierID) ON DepartmentInfo.EmployeeID = " & _
"CS_REQ.EmployeeID " & _
"IN '\\Svr-fp-rh3\groups\Purchasing Tracking Database Systems\Administration\Purchase Tracking System Admin_be.mdb' " & _
"WHERE (((Format([CSOrderID], "A0000")) Like "*" & [Forms]![POCreator]![POID] & "*")) " & _
"ORDER BY Format([CSOrderID],"A0000"); "

 

[PHV]

Tip: use single quotes for SQL literal strings, eg:

"SELECT Format([CSOrderID],[!]'[/!]\A0000[!]'[/!]) AS Requisition, " & _

Furthermore, the DoCmd.RunSQL method is for ACTION queries ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[3239]

Thank you PHV. That was very helpful.