469,090 Members | 1,085 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,090 developers. It's quick & easy.

Using Access With An SQL Find Function

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

Jul 17 '05 #1
1 7909
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

Jul 17 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

121 posts views Thread by typingcat | last post: by
3 posts views Thread by Random Person | last post: by
1 post views Thread by Daveyk0 | last post: by
10 posts views Thread by eyh5 | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.