Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    597
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Red face "Select statment includes a reserved word"

    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.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,173
    Thanks
    129
    Thanked 1,139 Times in 1,050 Posts
    I cannot understand why you are adding the last two characters to the SQL statement &;. I see nothing else wrong.

  4. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    597
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for the quick Response, however, I've removed the &; and still get the message. I'm open to any other ideas.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  5. #4
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,173
    Thanks
    129
    Thanked 1,139 Times in 1,050 Posts
    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.

  6. #5
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    597
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  7. #6
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,173
    Thanks
    129
    Thanked 1,139 Times in 1,050 Posts
    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.

  8. #7
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    597
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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)
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  9. #8
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,173
    Thanks
    129
    Thanked 1,139 Times in 1,050 Posts
    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.

  10. #9
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    597
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  11. #10
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,173
    Thanks
    129
    Thanked 1,139 Times in 1,050 Posts
    Great .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •