Like operator in SQL with Excel Driver

[sonu]

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

 

[Dick Kusleika]

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%'

 

[sonu]

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%'

I tried that also and it does not work either.

 

[Dick Kusleika]

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%'

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.