using LIKE operator with wildcard & variable in VBA

[Zammy]

I'm guessing this has been explained before, but I can not seem to
phrase the search correctly.
I am trying to use a string (strString) in a VBA select query. I want
to include the * wildcard in front of and behind the string.
How do I make this work?

[Rick Brandt]

Zammy wrote:

I'm guessing this has been explained before, but I can not seem to
phrase the search correctly.
I am trying to use a string (strString) in a VBA select query. I want
to include the * wildcard in front of and behind the string.
How do I make this work?

Dim sql as String

sql="SELECT * FROM TableName " & _
"WHERE FieldName Like '*" & strString & "*'"

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Zammy]

I'm sorry, my string is ssWord.
strTableName= tblSongs.

The code I entered was:

strSQL = "SELECT ArtistName,TrackName " & _
"FROM " & strTableName & _
" WHERE ArtistName = Like '*" & ssWord & "*' OR TrackName =
Like '*" & ssWord & "*';"

This is giving me a runtimeerror 3075

[Rick Brandt]

Zammy wrote:

I'm sorry, my string is ssWord.
strTableName= tblSongs.

The code I entered was:

strSQL = "SELECT ArtistName,TrackName " & _
"FROM " & strTableName & _
" WHERE ArtistName = Like '*" & ssWord & "*' OR TrackName =
Like '*" & ssWord & "*';"

This is giving me a runtimeerror 3075

You have both "=" and "Like" in the second criteria. Remove the "=" and you
should be fine.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Zammy]

Thank you. I was beating my head against the wall.

[Rick Brandt]

Zammy wrote:

Thank you. I was beating my head against the wall.

Always a good idea when building up complex strings to send the result to the
debug window so you can examine the result. Stuff like that would have been
very easy to spot.

Debug.Print VariableName

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com