SQL Query, on-the-fly

[giff256]

Hi all,
I was wondering whether you could help me? I'm trying to creating a query on the fly so i can take some from and to dates to find a best selling product in my db. To do this i need a SQL query which groups the productIDs together then sums the number of that product sold. This is the code i have so far and it returns the error -
Run-time error '3141'
The select statement includes a reserved word or arguement name that is mis-spelled or missing, or the punctuation is incorrect
Here is the code -
Private Sub cmdBestSelling_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQLBestSelling As String
Dim BestProductID As String
Dim BestProductName As String
Set db = CurrentDb
Set qdf = db.QueryDefs("qryBest")
strSQLBestSelling = "SELECT TOP 1 ProductID, Sum(NoSold) AS SumOfNoSold"
strSQLBestSelling = strSQLBestSelling + "FROM tblSold"
strSQLBestSelling = strSQLBestSelling + "WHERE DateSold BETWEEN #" & Me.txtFromDate.Value & "# AND #" & Me.txtToDate.Value & "#"
strSQLBestSelling = strSQLBestSelling + "GROUP BY ProductID"
strSQLBestSelling = strSQLBestSelling + "ORDER BY Sum(NoSold) DESC;"
qdf.SQL = strSQLBestSelling
BestProductID = DLookup("[ProductID]", "qryBest")
BestProductName = DLookup("[ProductName]", "tblProduct", "[ProductID] = " & BestProductID & "")
txtBestProductName.Value = BestProductName
End Sub
Can anyone shed some light on this?
Thank you

 

[Rabbie]

it looks like you are not including spaces in places where there should be.
I have also inserted a diagnostic print so you can see the SQL you have generated. This will make it easier to see any funnies.

Private Sub cmdBestSelling_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef 
Dim strSQLBestSelling As String
Dim BestProductID As String
Dim BestProductName As String
Set db = CurrentDb
Set qdf = db.QueryDefs("qryBest")
strSQLBestSelling = "SELECT TOP 1 ProductID, Sum(NoSold) AS SumOfNoSold"
strSQLBestSelling = strSQLBestSelling & " FROM tblSold"
strSQLBestSelling = strSQLBestSelling & " WHERE DateSold BETWEEN #" & Me.txtFromDate.Value & "# AND #" & Me.txtToDate.Value & "#"
strSQLBestSelling = strSQLBestSelling & " GROUP BY ProductID"
strSQLBestSelling = strSQLBestSelling & " ORDER BY Sum(NoSold) DESC;"
msgbox(strSQLBestSelling)
qdf.SQL = strSQLBestSelling
BestProductID = DLookup("[ProductID]", "qryBest")
BestProductName = DLookup("[ProductName]", "tblProduct", "[ProductID] = " & BestProductID & "")
txtBestProductName.Value = BestProductName
End Sub

Try this and see how it goes.

Good luck

 

[giff256]

Yeah it was them damn spaces! All working now,
Cheers for your reply

 

[Rabbie]

Yeah it was them damn spaces! All working now,
Cheers for your reply

Happy to help