like and wild card use in access 2003 and select statement in vb6

[deekshitulu]

Hai Every body

I am using Ms Access 2003 and Vb6
Table name is : accgeneral
field name is : Author
Text box name : Text1
Listview1 is the name of the Listview control


'*b*' is working in Access query

But it is not working as Sql Select statement like below:

rs.Open "Select * from accgeneral where Author like '*" & Text1.Text & "*'", db, 3, 3

is not working, it simply clear the listview control in run mode, it is not giving any errors, but not working.

My requirement is find the character where in the name of the author

above line code is working in access query created from the table accgeneral


The below code is working well.

Private Sub txtsearch_Change()
ListView1.ListItems.Clear
Dim list As ListItem
Dim x As Integer
connectDB
rs.Open "Select * from accgeneral where AccNo like '" & txtsearch.Text & "%'", db, 3, 3
Do Until rs.EOF
Set list = ListView1.ListItems.add(, , rs(0))
For x = 1 To 12
    If Not IsNull(rs(x)) Then
        list.SubItems(x) = rs(x)
    End If
Next x
rs.MoveNext
Loop
Set rs = Nothing
db.Close: Set db = Nothing
End Sub

Thanks in advance

[DataMiser]

So what is the question?

[techgnome]

Wrong wildcard... ADO recognizes the % as the wildcard... not * ... adjust code as necessary.

that's why this does work:
accgeneral where AccNo like '" & txtsearch.Text & "%'"

and this doesn't:
Author like '*" & Text1.Text & "*'"



-tg

[deekshitulu]

Mr.tg
Thanks for your responce

the query '*b*' is tested on the table accgeneral in query design mode in access, 2003. working well.

instead of b a Text box is used to pass the string on the form

my requirement is the above query have to be write in code as a select statement. I wrote the line of code below:

rs.open"select*from accgeneral Author like '*" & Text1.Text & "*'",db,3,3

is not working when i run the program. in run mode simply it clear the listview1 control.

Hope your response

Thanks

[brss]

Try the code below hope it's working.

rs.open "SELECT * FROM [accgeneral] Where [Author] LIKE '%" & Trim(Text1) & "%'", db, 3, 3

[brss]

Duplicate post. Sorry

[deekshitulu]

Thanks all who respond to this thread

I modify the line of code as below:

rs.open "select*from accgeneral where Author like '%"& Text1.Text &"%'",db,3,3

now working as per expectations

Thanks once again

[dilettante]

Note that this isn't ADO, which doesn't know anything about SQL at all.

It's the Jet 4.0 database engine, which uses a later version of SQL syntax when accessed via the OLEDB Provider you'd normally use when using ADO as your data access library.

See the article Fundamental Microsoft Jet SQL for Access 2000 in the MSDN Library documentation that comes with VB6.

[techgnome]

Thanks all who respond to this thread

I modify the line of code as below:

rs.open "select*from accgeneral where Author like '%"& Text1.Text &"%'",db,3,3

now working as per expectations

Thanks once again

I'm sorry... isn't that what I said to do in the first place? I'm sorry I didn't just copy your code and fix it for you directly... how stupid and lazy of me.

-tg