String Function in Excel ODBC

hiteshkataria · #1 (**permalink**) 02-15-10, 12:14

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().

MikeTheBike · #2 (**permalink**) 02-16-10, 09:33

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

hiteshkataria · #3 (**permalink**) 02-16-10, 09:53

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

hiteshkataria · #4 (**permalink**) 02-16-10, 12:05

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.

MikeTheBike · #5 (**permalink**) 02-17-10, 09:54

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

Internet Services

Web Development

Digital Marketing

Consumer Tech