Run-Time Error '1004' General ODBC Error

[gencom99]

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?

 

[HarleyQuinn]

Before your Query.Refresh (False) put in the line

Debug.Print Query.Sql

and put a break on that line. The SQL produced will be what you're trying to use. Copy it and run it against your DB seperately and see if it produces any errors in there (they're easier to diagnose in the native SQL environment normally).

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post

 

[SkipVought]

Hi,

What is in NumList?

Is Numb_Code TEXT or NUMERIC? If the former, is each list item enclosed as 'item1','item2'. If the latter item1,item2

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[gencom99]

I copied the code and put it before Query.Refresh (False).

I put a break on this line--Debug.Print Query.Sql

How do I copy it?

 

[SkipVought]

Copy it from the Immediate Window, after it executes the Debug.Print statement.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[gencom99]

After I ran it, there was no code in the Immediate window

 

[SkipVought]

You executed the Debug.Print statement and got NOTHING in the Immediate Window?

Then there is nothing in Query.Sql.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[gencom99]

When I put Msgbox Query.Sql before Debug.Print Query.Sql

I saw the SQL

 

[SkipVought]

Then you DO have text in the immediate window if you executed the Debug.Print Query.Sql statement.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[HarleyQuinn]

If for some reason you can't see the immediate window press Ctrl+G to bring it up. Also, you actually have to go to the line after the Debug.Print... before it will appear in the immediate window.

You could of course, also copy the text of the msgbox (Ctrl+C when it's active) and use that if you still have trouble with the immediate window.

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post