Private Sub CommandButton1_Click() Dim NumList As String Dim SourceBook As Workbook Dim ListSheet As Worksheet Dim QuerySheet As Worksheet Dim Query As QueryTable Dim Numb As String Set SourceBook = Application.ActiveWorkbook Set ListSheet = SourceBook.Worksheets("Main") Set QuerySheet = SourceBook.Worksheets("MONEY") Numb = Range("H6") If ListSheet.Cells(6, 8).Value = "" Then Exit Sub NumList = "'" & ListSheet.Cells(6, 8).Value & "'" Set Query = QuerySheet.QueryTables("MNY") Query.Sql = "SELECT NUMB_TBLE.NUMB_NAME, TO_CHAR(NUMB_DATES_TBLE.DAY_MTH_YR, 'MONTH'), TO_CHAR(NUMB_DATES_TBLE.DAY_MTH_YR, 'YYYY'), SUM(NUMB_TBLE. PAY_IN), SUM(NUMB_TBLE. PAY_OUT), SUM(NUMB_TBLE. NET_PAY) "_ & "FROM JES.NUMB TBLE NUMB TBLE, JES.NUMB_DATES_TBLE NUMB_DATES_TBLE" _ & "WHERE NUMB_TBLE.NUMB_ID = NUMB_DATES_TBLE.NUMB_ID AND NUMB_TBLE.NUMB_CODE IN (" & NumList & ") " _ & "Group by numb_tble.numb_name, TO_CHAR(NUMB_DATES_TBLE.DAY_MTH_YR, 'YYYY'), TO_CHAR(NUMB_DATES_TBLE.DAY_MTH_YR, 'MONTH') Query.Refresh (False) End Sub
When I add SUM(NUMB_TBLE. NET_PAY) to the code, I receive General ODBC error.
Before I add this code, the query works.
What is going on?
How do I fix it?
|