Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2010
    Posts
    22

    Unanswered: String Function in Excel ODBC

    Hi,

    I want to use the below Query using Excel ODBC driver:

    Code:
    strSQL = "SELECT PartyCode, Count(Cost), Sum(Cost)"
    strSQL = strSQL & ", SUM(Iif(Left(BillNo,1) Not In (""D"",""R""),1,0)) "
    
        rsItems.Open strSQL, strCnn, , , 1
    However, I am getting an Error:
    [Microsoft][ODBC Excel Driver] Too few parameters. Expected 2.

    The problem is Use of LEFT() function.
    The query works fine, if I omit the Left().

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    I don't see how the query as posted could work with or without the Left() function!?

    Perhaps you could post the full SQL.

    Is strCnn actualy a (open) connection object or just the connection string (not the same thing!)?

    Then again this could just be me not seeing the obvious!!


    MTB

  3. #3
    Join Date
    Feb 2010
    Posts
    22
    Here is the actual code:
    Code:
    Private Sub CommandButton1_Click()
    Dim rsItems As Object
    Dim arrItems
    Dim strCnn As String
    Dim strSQL As String
        strCnn = "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=C:\Book3.xls;DefaultDir=c:\;"
        
        Set rsItems = CreateObject("ADODB.Recordset")
       
        strSQL = "SELECT PartyCode, Count(Cost), Sum(Cost)"
        strSQL = strSQL & ", SUM(Iif(Left(BillNo,1) Not In (""D"",""R""),1,0)) "
        strSQL = strSQL & " FROM [A$]"
        strSQL = strSQL & " GROUP BY PartyCode "
        
        rsItems.Open strSQL, strCnn, , , 1
        
        arrItems = rsItems.GetRows
         
        arrItems = Application.Transpose(arrItems)
        
        ComboBox1.List = arrItems
         
    End Sub

  4. #4
    Join Date
    Feb 2010
    Posts
    22
    I guess, the problem is with the data.

    Is it possible to convert or change the Format of column containing data. I need to convert General to Text.


    The query works fine, if I have a new column with Text format, only change is to make use of Single quotes instead of Double quotes.

  5. #5
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    I've had a look at this (I don't connect to excel by ODBC - always put data in a database, so have learned something hear).

    I woud seem that the Left() function works OK, but cannot get the IIF() function to work, which I suggext is the problem (works ok with Access connection!).

    My only suggestion is to add another field (column) called, say, 'NotDR' to the spreadsheet to do that part of the calculation there ie. somethink like
    =IF(AND(LEFT(C2,1)<>"R",LEFT(C2,1)<>"D"),1,0)

    and then add Sum(NotRD) to the query like
    Code:
    Private Sub CommandButton1_Click()
    Dim rsItems As Object
    Dim arrItems
    Dim strCnn As String
    Dim strSQL As String
        strCnn = "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=C:\Book3.xls;DefaultDir=c:\;"
        
        Set rsItems = CreateObject("ADODB.Recordset")
       
        strSQL = "SELECT PartyCode, Count(Cost), Sum(Cost), Left(BillNo,1) as Lft"
        strSQL = strSQL & ", Sum(NotDR) as SUMNotDR "
        strSQL = strSQL & " FROM [A$] "
        strSQL = strSQL & " GROUP BY PartyCode,Left(BillNo,1) "
        strSQL = strSQL & " Order By Left(BillNo,1)"
    
        rsItems.Open strSQL, strCnn, , 1
    
        arrItems = rsItems.GetRows
        arrItems = Application.Transpose(arrItems)
        
    '    ComboBox1.List = arrItems
         
    End Sub
    HTH !?


    MTB

Posting Permissions

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