There is a reserved word in my SQL statement

Question

I am connecting to a database in Visual Basic. When I connect, I would like to run the following query and output the results to the spreadsheet. The query gives me expected results when I run it in Access. However I do not know how to translate the query to VB syntax.

Can anyone tell me what is wrong with this SQL statement or why I get the error message that there is a reserved word in my SQL statement?

Dim SQL As String
Dim numrows As Integer

Range("B1").Value = CustFirstName & CustLastName

SQL = "SELECT Orders.OrderDate, Sum(QuotedPrice*QuantityOrdered) AS TotalCost" _
& "FROM Orders INNER JOIN LineItems ON Orders.OrderID = LineItems.OrderID" _
& "WHERE (((Orders.customerID) = [Customer].[customerID]))" _
& "GROUP BY Orders.OrderID, Orders.OrderDate" _
& "ORDER BY Orders.OrderDate"

Not sure about a reserved word, but the spacing is likely to be confusing the query parser. Examine the runtime value of `SQL`. I suspect you have things like `TotalCostFROM` and `LineItems.OrderIDWHERE` in there. It's possible that the query parser is successfully misinterpreting this and by coincidence alone thinks some of the keywords you're using are meant to be identifiers. — David, Dec 07 '19 at 18:49
I’m not 100% that’s the *actual* SQL / error.. try to run the resulting string in SSMS. — user2864740, Dec 07 '19 at 18:49
@riddlemethisagain: When you run *what* in Access, specifically? Isn't this code already in Access? — David, Dec 07 '19 at 18:52
As @David mentioned add a blank at the end of every line in your statemen — Jens, Dec 07 '19 at 18:53
I added a space and now I get an error that says "no value given for one or more required parameters" — riddlemethisagain, Dec 07 '19 at 18:59
@riddlemethisagain: If you've modified the code and are getting a different error, please edit the question to include that updated information. — David, Dec 07 '19 at 19:01
Why are you building an aggregate query in VBA? What do you intend to do with it? — June7, Dec 07 '19 at 19:30

score 2 · Answer 1 · edited Dec 07 '19 at 19:31

Your SQL string is wrong. It is missing FROM clause and also WHERE, GROUP BY, ORDER BY are all written wrong. There should be at least one white space before those keywords, but you have things like:

SQL = "... TotalCost" & "FROM ..."

which makes it " .... TotalCostFROM ...". Same for WHERE, GROUP BY, ORDER BY. Try rewriting it as:

SQL = "SELECT Orders.OrderDate, Sum(QuotedPrice*QuantityOrdered) AS TotalCost" _
& " FROM Orders INNER JOIN LineItems ON Orders.OrderID = LineItems.OrderID" _
& " GROUP BY Orders.OrderID, Orders.OrderDate" _
& " ORDER BY Orders.OrderDate"

Also I removed the WHERE part, because it contains [Customer].[customerID] which doesn't exist.

And be careful about the result, you are grouping by OrderId and then OrderDate. Result might have multiple entries for the same date (different Ids).

June7 · Answer 2 · 2019-12-07T19:46:59.043

1

Along with missing separator space at beginning or end of each continuation line, WHERE clause is incorrect. A variable input must be concatenated. Reference to form field/control is a variable input.

SQL = "SELECT Orders.OrderDate, Sum(QuotedPrice*QuantityOrdered) AS TotalCost " _
& "FROM Orders INNER JOIN LineItems ON Orders.OrderID = LineItems.OrderID " _
& "WHERE Orders.customerID = " & Me.customerID & " " _
& "GROUP BY Orders.OrderID, Orders.OrderDate " _
& "ORDER BY Orders.OrderDate"

If SQL injection is a concern, use PARAMETERS instead of concatenation. How does the SQL injection from the "Bobby Tables" XKCD comic work?

edited Dec 07 '19 at 19:46

answered Dec 07 '19 at 19:24

June7

19,874
8
24
34

You should never write such an SQL string with concatenation and use parameters instead. – Cetin Basoz Dec 07 '19 at 19:31
Really depends how CustomerID parameter is provided. If it is user input via a LimitToList combobox or reference to a control that user does not fill in, Parameters likely not necessary. But edited my question to add comment about SQL injection. – June7 Dec 07 '19 at 19:36

There is a reserved word in my SQL statement

2 Answers2