Problem wit SQL-Statement

[raindrop3]

Hello,

I want to search in a table. In a textbox the user had to type the name of the name of the person he is looking for.
In the table is a person called "Marinus". I want, when the user types "Mari" that "Marinus" is found.

My SQL statement looks like this:

Dim rstthumbs As Recordset
Dim dbs As Database
Dim Txtz As Control

Set dbs = CurrentDb()
Set rstthumbs = dbs.OpenRecordset("SELECT * FROM tblNaam")
Set Txtz = Me!zoeknaam 'the textbox where the name is typed

--------------------------------------------------------------------

The 'criteria checker' looks like this:

If rstthumbs("naam") Like Txtz Then GoTo Found
rstthumbs.MoveNext
Loop

The 'Like' part doesn't work!

Can someone help me please?

Thanks in advantage.

Albert

 

[Chris RR]

I would put the "like" into a where clause in your SQL statement. That way, you only will select the correct records in the first place. This code is off the top of my head and you may need to correct the quotes:

Dim strSQL as String
...
strSQL = "SELECT * FROM tblNaam WHERE naam like '*" & Me.zoeknaam & "*'"
Set rstthumbs = dbs.OpenRecordset(strSQL)

 

[The_Doc_Man]

Chris is pretty much right.

"Like" when you don't have a wildcard in the string is identical to "=" in effect (but a little slower).

assume stQuery is a string, rsQry is a recordset, dbX is your database (already open), [Naam] is the control on your form, [BunchaNames] is your table, and [AName] is the critical field in the table.

stQuery = "SELECT * FROM [BunchaNames] WHERE [AName] LIKE ""*" & Trim$([Naam]) & "*"""

set rsQry = dbX.OpenRecordSet(stQuery)

Watch out for the doubled quotes in the string. The above gives you a match on the contents of [Naam] anywhere in [AName], not just beginning with [Naam].

 

[raindrop3]

Thanks for the replys.

I tryed both things and now it works great. The explanation from the_doc_man helps me a lot.

Thanks again.

Albert