CODEWITHSUNDEEP
excelvbaqodbc
marian
marian

Reputation: 1

How do I fix Run-Time error '5' in .CommandText Array(... SELECT ....) statement

I am retrieving customers last month monthly recuring charges from QuickBooks into Excel sheet, using QODBC driver. It is working OK, until I add one more field from the Customer record. It fails with run-time error '5': Invalid procedure call or Argument.

The SELECT statement with a field name Customer.FullName being one of the fields, it works fine. If I replace Customer.FullName with Customer.IsActive field, it works fine also. But, If I add the field, "Customer.FullName, Customer.IsActive, ", executing the SELECT statement gives the error. I researched the issue, but cannot find a reason why or how to solve it.

This code fails:

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
    "ODBC;DSN=QuickBooks Data QRemote;SERVER=QODBC;OptimizerDBFolder=%UserProfile%\QODBC Driver for QuickBooks\Optimizer;OptimizerAllowDirtyReads" _
    ), Array("=N;SyncFromOtherTables=N;ForceSDKVersion=<default SDK>")), _
    Destination:=Range("$A$1")).QueryTable
'        .CommandText = CmdString
'        .CommandText = Array(SelClause, FromClause, WhereClause)

    .CommandText = Array( _
    "SELECT Charge.CustomerRefListID, Customer.FullName, Customer.IsActive, Charge.TxnDate, Item.FullName, Charge.Rate, Charge.""Desc""" & Chr(13) & "" & Chr(10) & _
     "FROM Charge Charge, Customer Customer, Item Item" & Chr(13) & "" & Chr(10) & _
     "WHERE Charge.CustomerRefListID = Customer.ListID AND Charge.ItemRefListID = Item.ListID AND " _
    , "((Charge.TxnDate=" & formateddate & ") AND (Item.SalesOrPurchaseAccountRefFullName='Weekly Services'))")
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .ListObject.DisplayName = "Table_Query_Charges_from_QuickBooks_Data"
    .Refresh BackgroundQuery:=False
End With

This SELECT statement in the below code works OK.

    .CommandText = Array( _
    "SELECT Charge.CustomerRefListID, Customer.FullName, Charge.TxnDate, Item.FullName, Charge.Rate, Charge.""Desc""" & Chr(13) & "" & Chr(10) & _
     "FROM Charge Charge, Customer Customer, Item Item" & Chr(13) & "" & Chr(10) & _
     "WHERE Charge.CustomerRefListID = Customer.ListID AND Charge.ItemRefListID = Item.ListID AND " _
    , "((Charge.TxnDate=" & formateddate & ") AND (Item.SalesOrPurchaseAccountRefFullName='Weekly Services'))")

This works also.

    .CommandText = Array( _
    "SELECT Charge.CustomerRefListID, Customer.IsActive, Charge.TxnDate, Item.FullName, Charge.Rate, Charge.""Desc""" & Chr(13) & "" & Chr(10) & _
     "FROM Charge Charge, Customer Customer, Item Item" & Chr(13) & "" & Chr(10) & _
     "WHERE Charge.CustomerRefListID = Customer.ListID AND Charge.ItemRefListID = Item.ListID AND " _
    , "((Charge.TxnDate=" & formateddate & ") AND (Item.SalesOrPurchaseAccountRefFullName='Weekly Services'))")

Both fields, Customer.FullName and Customer.IsActive, are valid fields and the SELECT statement works fine with one of them. How can I make it work with both fields in the SELECT statement?

Upvotes: 0

Views: 626

Answers (1)

marian
marian

Reputation: 1

I did more experiments and changing the select statement from Array() to String worked OK. ......

   CmdString = "SELECT Charge.CustomerRefListID, Customer.FullName, Customer.IsActive, Charge.TxnDate, Item.FullName, Charge.Rate, Charge.""Desc"" FROM Charge Charge, Customer Customer, Item Item WHERE Charge.CustomerRefListID = Customer.ListID AND Charge.ItemRefListID = Item.ListID AND ((Charge.TxnDate=" & formateddate & ") AND (Item.SalesOrPurchaseAccountRefFullName='Weekly Services'))"
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
    "ODBC;DSN=QuickBooks Data QRemote;SERVER=QODBC;OptimizerDBFolder=%UserProfile%\QODBC Driver for QuickBooks\Optimizer;OptimizerAllowDirtyReads" _
    ), Array("=N;SyncFromOtherTables=N;ForceSDKVersion=<default SDK>")), _
    Destination:=Range("$A$1")).QueryTable
    .CommandText = CmdString
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .ListObject.DisplayName = "Table_Query_Charges_from_QuickBooks_Data"
    .Refresh BackgroundQuery:=False
End With

........

Upvotes: 0

Related Questions