SQL with wildcard?

[softwareguy74]

Hi,

I'm trying to construct a "wildcard" SQL statement such as:


Dim sSQL as String
Dim sSearchFor as String

sSearchFor = Text1.Text

sSQL = "SELECT * " _
& "FROM tblTest " _
& "WHERE Field1 LIKE '*" & sSearchFor & "*'"

Set rs = New RecordSet

rs.Open sSQL, db


The idea being that the user can enter any text in Text1 and it will find any record where Field1 contains the text that the user entered..

But, it does not seem to be returning any records. It does not seem to like the wildcards.. It doesn't error out, but it just doesn't return any records... And yes, I have verified that the text does in fact exist in the database.. When I remove the * signs, then the records are returned, if I enter the whole Field1 text..

Any idea on how to make this thing work?

Thanks,

Dan

try this:


Dim sSQL as String
Dim sSearchFor as String

sSearchFor = Text1.Text

sSQL = "SELECT * FROM tblTest " _
& "WHERE Field1 LIKE '%" & sSearchFor & "%'"

Set rs = New RecordSet
rs.Open sSQL, db


cause when using LIKE in SQL, the % sign that comes in front of or behind (a) letter(s) stands for the wildcard.

[softwareguy74]

Thank you, that worked!!!

Dan

no prob. glad to have helped.

[JHausmann]

It's going to depend on the DBMS and what you use to access it. Microsoft Access (with DAO) will use the "*" in place of the "%"

[sanon]

More information

I have used a "*" for query like that and it works fine for me. I use ADO and SQL Server 7.0.