Using Access With An SQL Find Function

[Brian Basquille]

Hello all,

Hope someone can help me with this! It would be much appreciated!

Am using a Access 2000 Database (actcs2002.mdb) integrated into VB6. I am
having trouble writing a Find command (when the user provides a part of a
surname into a TextBox and clicks the Find button, it should return the
surname of the customer and their appropriate customer number in a ListBox)

A few examples would be:

Surnames: Cust_No:
Woods 1
Wolf 2
Byrnes 3

So if the user inputs ' Wo* ' - it would display:

Woods 1
Wolf 2

Or if the user inputs ' Byrnes ' - it would display:

Byrnes 3

I think you all get the picture anyways [ by the way, hope i didn't over
simplify (if that's the right word) things with that example ]. I am using
an SQL statement to find the customer in the database and output their
surname and number.

Here's what i have so far.

Private Sub cmdFind_Click()
Set conn1 = New ADODB.Connection
conn1.Provider = "microsoft.jet.oledb.4.0;"
conn1.ConnectionString = App.Path & "\accts2002.mdb"
conn1.Open

x = "Select * From Customer Where Sname Like " & txtSname.Text

Set rs2 = New ADODB.Recordset
rs2.Open x, conn1
rs2.MoveFirst

Do While Not rs2.EOF
y = rs2!c_no & " " & rs2!sname
lstResults.AddItem y
rs2.MoveNext
Loop
End Sub

[John Lauwers]

Brian,

Use wildcards,

x = "Select * From Customer Where Sname Like '%" & txtSname.Text & "%'"
greets John

--
-----------------------------------------------
John Lauwers
Robotronic Nv
jo**@robotronic.be

"Brian Basquille" <tb**************@eircom.net> schreef in bericht
news:eY*****************@news.indigo.ie...

Hello all,

Hope someone can help me with this! It would be much appreciated!

Am using a Access 2000 Database (actcs2002.mdb) integrated into VB6. I am
having trouble writing a Find command (when the user provides a part of a
surname into a TextBox and clicks the Find button, it should return the
surname of the customer and their appropriate customer number in a ListBox)
A few examples would be:

Surnames: Cust_No:
Woods 1
Wolf 2
Byrnes 3

So if the user inputs ' Wo* ' - it would display:

Woods 1
Wolf 2

Or if the user inputs ' Byrnes ' - it would display:

Byrnes 3

I think you all get the picture anyways [ by the way, hope i didn't over
simplify (if that's the right word) things with that example ]. I am using
an SQL statement to find the customer in the database and output their
surname and number.

Here's what i have so far.

Private Sub cmdFind_Click()
Set conn1 = New ADODB.Connection
conn1.Provider = "microsoft.jet.oledb.4.0;"
conn1.ConnectionString = App.Path & "\accts2002.mdb"
conn1.Open

x = "Select * From Customer Where Sname Like " & txtSname.Text

Set rs2 = New ADODB.Recordset
rs2.Open x, conn1
rs2.MoveFirst

Do While Not rs2.EOF
y = rs2!c_no & " " & rs2!sname
lstResults.AddItem y
rs2.MoveNext
Loop
End Sub