SQL with Like statement and wildcards...

[Guest]

Hello all,

I am having trouble using an SQL statement in vb, where the Where clause is
Like and wild cards on both ends.

I have tried many different syntax, but nothing seems to work.

Here are some things I have tried

vCriteria = “*†& txLkUp & “*†‘Using this to add the wildcard
vSQL = “Select * from tb where fld like vCriteria

vCriteria = txLkUp
vSQL = “Select * from tb where fld like “*†& vCriteria & “*â€

vCriteria = txLkUp
vSQL = “Select * from tb where fld like vCriteria

The list could go on and on, but apparently not to the right way. Can
someone shed some light on this? Also, the field is a text field.

Thanks

 

[Dirk Goldgar]

Hello all,

I am having trouble using an SQL statement in vb, where the Where
clause is Like and wild cards on both ends.

I have tried many different syntax, but nothing seems to work.

Here are some things I have tried

vCriteria = "*" & txLkUp & "*" 'Using this to add the wildcard
vSQL = "Select * from tb where fld like vCriteria

vCriteria = txLkUp
vSQL = "Select * from tb where fld like "*" & vCriteria & "*"

vCriteria = txLkUp
vSQL = "Select * from tb where fld like vCriteria

The list could go on and on, but apparently not to the right way. Can
someone shed some light on this? Also, the field is a text field.

Thanks

You need to get the quotes and the value of vCriteria into the SQL
string. Try this:

vSQL = "Select * from tb where fld like ""*" & vCriteria & "*"""

Or you can make that more readable this way:

vSQL = "Select * from tb where fld like " & _
Chr(34) & "*" & vCriteria & "*" & Chr(34)

If you're sure that vCriteria won't contain the single-quote character,
you might use this, which is more readable still:

vSQL = "Select * from tb where fld like '*" & vCriteria & "*'"

 

[David C. Holley]

Try using 'LIKE' as in vCriteria = " Like '*'" & txtLkUp & "'*'"
note the nexted single quotes