Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Like operator in SQL with Excel Driver
Hi,
I am using adodb recorset and excel driver to query a worksheet in excel file. When I try to use like poerator it gives me an error. Can someone please help me. Below is my code When I remove % and replace it with correct part number it shows the correct results. But my part number column has more part number and I just need to count records that contains part number I supplied. Sub PartNumberUsage() Dim condb As New ADODB.Connection wk_selected = ActiveSheet.Name wk_xtab = "MSO_Xtab" dbpath = ActiveWorkbook.FullName conn = "DRIVER={Microsoft Excel Driver (*.xls)}; ReadOnly=1;DBQ=" & dbpath condb.Open conn Set rst = New ADODB.Recordset Sql = "select count(" & Worksheets(wk_xtab).Range("A1").Value & ") from [MSO_Xtab$A:iv] where Part_no = '301971%' " Call rst.Open(Sql, condb, CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockReadOnly, CommandTypeEnum.adCmdText) Cells(ActiveCell.Row, ActiveCell.Column + 1).Value = rst(0) Application.StatusBar = False rst.Close Set rst = Nothing Set condb = Nothing |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Like operator in SQL with Excel Driver
On Wed, 23 Jul 2008 10:04:24 -0700 (PDT), sonu wrote:
Set rst = New ADODB.Recordset Sql = "select count(" & Worksheets(wk_xtab).Range("A1").Value & ") from [MSO_Xtab$A:iv] where Part_no = '301971%' " Shouldn't that be where Part_no Like '301971%' -- Dick Kusleika Microsoft MVP-Excel http://www.dailydoseofexcel.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Like operator in SQL with Excel Driver
On Jul 23, 5:07*pm, Dick Kusleika wrote:
On Wed, 23 Jul 2008 10:04:24 -0700 (PDT), sonu wrote: Set rst = New ADODB.Recordset Sql = "select count(" & Worksheets(wk_xtab).Range("A1").Value & ") from [MSO_Xtab$A:iv] *where Part_no *= '301971%' " Shouldn't that be where Part_no Like '301971%' -- Dick Kusleika Microsoft MVP-Excelhttp://www.dailydoseofexcel.com I tried that also and it does not work either. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Like operator in SQL with Excel Driver
On Thu, 24 Jul 2008 05:37:51 -0700 (PDT), sonu wrote:
On Jul 23, 5:07*pm, Dick Kusleika wrote: On Wed, 23 Jul 2008 10:04:24 -0700 (PDT), sonu wrote: Set rst = New ADODB.Recordset Sql = "select count(" & Worksheets(wk_xtab).Range("A1").Value & ") from [MSO_Xtab$A:iv] *where Part_no *= '301971%' " Shouldn't that be where Part_no Like '301971%' -- Dick Kusleika Microsoft MVP-Excelhttp://www.dailydoseofexcel.com I tried that also and it does not work either. I created Book1.xls with a simple table. In Book2.xls, I put this macro Sub Test() Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Dim sCn As String, sRs As String sCn = "DSN=Excel Files;DBQ=C:\Documents and Settings" sCn = sCn & "\Dick.NEBRASKA\My Documents\Book1.xls;" sCn = sCn & "DefaultDir=C:\Documents and Settings\" sCn = sCn & "Dick.NEBRASKA\My Documents;DriverId=1046;" sCn = sCn & "MaxBufferSize=2048;PageTimeout=5;" sRs = "SELECT MyData.Part_no, MyData.Name" sRs = sRs & " FROM MyData WHERE MyData.Part_no LIKE '123%'" Set cn = New ADODB.Connection cn.Open sCn Set rs = cn.Execute(sRs) Stop rs.Close: cn.Close Set rs = Nothing: Set cn = Nothing End Sub And it worked. Maybe you can see some differences with your code. I also changed the sql statement to sRs = "SELECT count(MyData.Part_no)" sRs = sRs & " FROM MyData WHERE MyData.Part_no LIKE '123%'" and it worked. -- Dick Kusleika Microsoft MVP-Excel http://www.dailydoseofexcel.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to install two video adapter driver using the same WDDM driver? | New Users to Excel | |||
Postscript driver won't print rows that the PCL driver will | Excel Discussion (Misc queries) | |||
Excel Driver | Excel Discussion (Misc queries) | |||
Excel Driver | Excel Discussion (Misc queries) | |||
Excel Driver | Excel Programming |