Trying to use Asterik (*) in sql

[Guest]

I am trying to create an Sql string to assign to a variable that i can use to
set the record source of a form. I am having a hard time inserting the "
qualifier around the *'s using a variable. PAsting the string as follows.
Any help will be greatly appreciated

ElseIf Not IsNull(Me.txtWhat.Value) And IsNull(Me.txtProblem) Then
strSQL = "SELECT tblLogOfIdeas.IdNo, tblLogOfIdeas.What,
tblLogOfIdeas.SymptomProblem, tblLogOfIdeas.ListedDate,
tblLogOfIdeas.ToActionDate " _
& "FROM tblLogOfIdeas " _
HERE--->>> & "WHERE (((tblLogOfIdeas.What)Like *" & strUserWhat & "*));"
Debug.Print strSQL
Forms!frmODLogOfIDeas!frmSubLOI.Form.RecordSource = strSQL

 

[Douglas J. Steele]

You need quotes before the first asterisk and after the last one.

& "WHERE (((tblLogOfIdeas.What) Like '*" & strUserWhat & "*'))"

Note that if strUserWhat contains an apostrophe (say it's a name like
O'Reilly), you'll run into problems with that though. Your options are
either to double the apostrophes in the string using Replace:

& "WHERE (((tblLogOfIdeas.What) Like '*" & Replace(strUserWhat, "'", "''") &
"*'))"

(In case it's not clear, the Replace statement is Replace(strUserWhat, " '
", " ' ' ") )

or else use double quotes (which will have to be doubled up inside a
string):

& "WHERE (((tblLogOfIdeas.What) Like ""*" & strUserWhat & "*""))"

or

& "WHERE (((tblLogOfIdeas.What) Like " & Chr$(34) & "*" & strUserWhat & "*"
& Chr$(34) & "))"