“Select statment includes a reserved word”

Topic

New Reply

I have looked over this code so long to find the error until my eyes are crossed. Could someone please review my code and see if you can determine why I’m getting the above error message?

Code:

strSQL = “SELECT ApVenTest.VenNumID, ApVenTest.VenName, Abs([GrossAmt]) AS GrsAmt” _
             & “FROM ApTest INNER JOIN ApVenTest ON ApTest.VenNum = ApVenTest.VenNumID” _
            & “GROUP BY ApVenTest.VenNumID, ApVenTest.VenName, Abs([GrossAmt])” _
            & “HAVING (((Count(ApTest.GrossAmt))>1)) &;”

Thanks in advance for assistance.

Reply | Quote

Replies

I cannot understand why you are adding the last two characters to the SQL statement &;. I see nothing else wrong.

Reply | Quote

Thanks for the quick Response, however, I’ve removed the &; and still get the message. I’m open to any other ideas.

Reply | Quote

You are also not leaving spaces at the end or the beginning of each “line”, so you get (…)

GrsAmtFROM (…), (…)VenNumIDGROUPBy(…), etc.

One good way to debug this is to actually print the value of the SQL statement after it is obtained. In Access you could do that with Debug.Print and adding a breakpoint to the Debug.Print strSQL statement will allow you to execute it and see the value of the actual SQL being executed. Many errors are easy to be found at that point.

Reply | Quote

I’m going to post my entire code so someone can perhaps show me the error of my ways.

Code:

On Error GoTo Err_cmdOpenQuery_Click
    Dim MyDB As DAO.Database
    Dim qdef As DAO.QueryDef
    Dim i As Integer
    Dim strSQL As String
    Dim strWhere As String
    Dim strIN As String
    Dim flgSelectAll As Boolean
    Dim varItem As Variant
    
    Set MyDB = CurrentDb()
    
       strSQL = “SELECT ApVenTest.VenNumID, ApVenTest.VenName, Abs([GrossAmt]) AS GrsAmt ” & _
             “FROM ApTest INNER JOIN ApVenTest ON ApTest.VenNum = ApVenTest.VenNumID ” & _
             “GROUP BY ApVenTest.VenNumID, ApVenTest.VenName, Abs([GrossAmt]) ” & _
             “HAVING (Count(ApTest.GrossAmt)>1)”

             
    ‘Build the IN string by looping through the listbox
    For i = 0 To lstVenNum.ListCount – 1
        If lstVenNum.Selected(i) Then
            If lstVenNum.Column(0, i) = “All” Then
                flgSelectAll = True
            End If
            strIN = strIN & “” & lstVenNum.Column(0, i) & “,”
        End If
     Next i
     
    ‘Create the WHERE string, and strip off the last comma of the IN string
    strWhere = ” WHERE [VenNumID] in (” & Left(strIN, Len(strIN) – 1) & “)”
    
    ‘If “All” was selected in the listbox, don’t add the WHERE condition
    If Not flgSelectAll Then
        strSQL = strSQL & strWhere
    End If
    
    MyDB.QueryDefs.Delete “qry_Vendor_rst”
    Set qdef = MyDB.CreateQueryDef(“qry_Vendor_rst”, strSQL)
    
    ‘Open the query, built using the IN clause to set the criteria
    DoCmd.OpenQuery “qry_Vendor_rst”, acViewNormal
    Me.Requery
    
    ‘Clear listbox selection after running query
    For Each varItem In Me.lstVenNum.ItemsSelected
        Me.lstVenNum.Selected(varItem) = False
    Next varItem
    
    
Exit_cmdOpenQuery_Click:
    Exit Sub
    
Err_cmdOpenQuery_Click:
   If Err.Number = 5 Then
        MsgBox “You must make a selection(s) from the list”, , “Selection Required !”
        Resume Exit_cmdOpenQuery_Click
    Else
    ‘Write out the error and exit the sub
        MsgBox Err.Description
        Resume Exit_cmdOpenQuery_Click
    End If
End Sub
Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click

    DoCmd.Close
Exit_cmdClose_Click:
    Exit Sub
Err_cmdClose_Click:
    MsgBox Err.Description
    Resume Exit_cmdClose_Click
    

Reply | Quote

You are creating a query named

qry_Vendor_rst

.

Are you able to open that query, change the view to SQL View, copy and past the full SQL here?

Alternatively, can you add a line with Debug.Print strSQL immediately before the query deletion statement (MyDB.QueryDefs.Delete…), set a breakpoint in the query deletion statement and run all that code? That will result in the SQL being printed in the immediate window. You can then post here. Without the whole SQL statement is very hard to find out what the problem is.

Reply | Quote

I really appreciate your help. I added the debug.print and breakpoint. Below is the code from the immediate window.

Code:

SELECT ApVenTest.VenNumID, ApVenTest.VenName, ApTest.GrossAmt 
FROM ApTest INNER JOIN ApVenTest ON ApTest.VenNum=ApVenTest.VenNumID 
GROUP BY ApVenTest.VenNumID, ApVenTest.VenName, ApTest.GrossAmt 
HAVING (((Count(ApTest.GrossAmt))>1)) WHERE [VenNumID] in (10547)

Reply | Quote

That statement has a problem, indeed. The WHERE clause cannot be the last one. It needs to be immediately after the FROM clause, before the GROUP BY:

Code:

SELECT ApVenTest.VenNumID, ApVenTest.VenName, ApTest.GrossAmt 
FROM ApTest INNER JOIN ApVenTest ON ApTest.VenNum=ApVenTest.VenNumID 
WHERE [VenNumID] in (10547)
GROUP BY ApVenTest.VenNumID, ApVenTest.VenName, ApTest.GrossAmt 
HAVING (((Count(ApTest.GrossAmt))>1)) 

This means you will have to change your code to add it in the proper place in the SQL statement.

Reply | Quote

I can’t thank you enough for the help. Thanks to your advice, I modified the code as you outlined and the entire process works as it should now.

Code:

On Error GoTo Err_cmdOpenQuery_Click
    Dim MyDB As DAO.Database
    Dim qdef As DAO.QueryDef
    Dim i As Integer
    Dim strSQL As String
    Dim strSQLGroup As String
    Dim strWhere As String
    Dim strIN As String
    Dim flgSelectAll As Boolean
    Dim varItem As Variant
    
    Set MyDB = CurrentDb()
    
    strSQL = “SELECT ApVenTest.VenNumID, ApVenTest.VenName, ApTest.GrossAmt” & vbCrLf & _
    “FROM ApTest INNER JOIN ApVenTest ON ApTest.VenNum=ApVenTest.VenNumID”
    
    strSQLGroup = “GROUP BY ApVenTest.VenNumID, ApVenTest.VenName, ApTest.GrossAmt” & vbCrLf & _
    “HAVING (((Count(ApTest.GrossAmt)) > 1))”
        
    ‘Build the IN string by looping through the listbox
    For i = 0 To lstVenNum.ListCount – 1
        If lstVenNum.Selected(i) Then
            If lstVenNum.Column(0, i) = “All” Then
                flgSelectAll = True
            End If
            strIN = strIN & “” & lstVenNum.Column(0, i) & “,”
        End If
     Next i
     
    ‘Create the WHERE string, and strip off the last comma of the IN string
    strWhere = ” WHERE [VenNumID] in (” & Left(strIN, Len(strIN) – 1) & “)”
    
    ‘If “All” was selected in the listbox, don’t add the WHERE condition
    If Not flgSelectAll Then
        strSQL = strSQL & strWhere & strSQLGroup
    End If
    
    MyDB.QueryDefs.Delete “qry_Vendor_rst”
    Set qdef = MyDB.CreateQueryDef(“qry_Vendor_rst”, strSQL)
    
    ‘Open the query, built using the IN clause to set the criteria
    ‘DoCmd.OpenQuery “qry_Vendor_rst”, acViewNormal
    Me.Requery
    
    ‘Clear listbox selection after running query
    For Each varItem In Me.lstVenNum.ItemsSelected
        Me.lstVenNum.Selected(varItem) = False
    Next varItem
    
    
Exit_cmdOpenQuery_Click:
    Exit Sub
    
Err_cmdOpenQuery_Click:
   If Err.Number = 5 Then
        MsgBox “You must make a selection(s) from the list”, , “Selection Required !”
        Resume Exit_cmdOpenQuery_Click
    Else
    ‘Write out the error and exit the sub
        MsgBox Err.Description
        Resume Exit_cmdOpenQuery_Click
    End If

Reply | Quote

Great :).

Reply | Quote