Access 2002. Form - sql. syntax usage of 'like operator' in sql

Access 20020. SQL/VB.  I am getting an error when using the LIKE operator for a string variable.
EXAMPLE:  (TABLENAME.[TABLENAME Name]) Like '" * val3 * "' ))   I have also tried using the '&' symbol.  I normally do not have this issue, however the * appears to be throwing it off ... any suggestions ?

Thank you.
ellenjbrAsked:
Who is Participating?
 
Computer101Connect With a Mentor Commented:
PAQed with points refunded (500)

Computer101
EE Admin
0
 
Patrick MatthewsCommented:
Hello ellenjbr,

Please post a more complete snippet of the code.

Regards,

Patrick
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
You cannot use a string variable in a query expression.

mx
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
ellenjbrAuthor Commented:
Since when ?
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Are you in code or a query expression?  I referring to a SQL stmt in the query grid ...

If in code, try this:

(TABLENAME.[TABLENAME Name]) Like Chr(34) & "*" & Chr(34) & val3 & Chr(34) & "*" & Chr(34) ))

mx

0
 
GRayLCommented:
This worked in the immediate pane:

v="finished"
docmd.RunSQL "Delete from absameline1 where f like ""*" & v & "*"";
0
 
GRayLCommented:
TABLENAME.[TABLENAME Name]) Like ""*" & val3 & "*""

that work?
0
 
ellenjbrAuthor Commented:
This is the syntax used in sql  for a string variable is: '" & val1 & "', FYI.   I simply need to know the difference as to proper syntax for a string variable when the operator 'LIKE' is added to the formula and the wildcard * ...Thank you.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Have you tried any of the above posts ?

mx
0
 
GRayLCommented:
I just showed you.
0
 
GRayLCommented:
TABLENAME.[TABLENAME Name]) Like ""*" & val3 & "*""

that work?
0
 
GRayLCommented:
from the immediate pane:

v="finished"
docmd.RunSQL "Delete from absameline1 where f like ""*" & v & "*"";

and I was prompted if I wanted to delete one record.
0
 
PaulHewsCommented:
(supporting the answers given by DatabaseMX and GRayL)

If you are using apostrophe to delineate string values, using the incomplete example you gave:

strSql = "(TABLENAME.[TABLENAME Name]) Like '*" & val3 & "*'))"

If using quotes in a string, you must double it to escape it, as the quote character terminates a string constant in VB.

strSql = "(TABLENAME.[TABLENAME Name]) Like ""*" & val3 & "*""))"

When debugging sql in VB, it's useful to use a Debug.Print to write it to the debug window.  It will be easier to see where the errors lie.  You can also paste from there into the SQL pane of a new query to get even more information about the problem.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
ellen ... note that Chr(34) is a double quote character.  I use Chr(34) for clarity of posting.

mx
0
 
ellenjbrAuthor Commented:
DatabaseMx - Yes, and nothing has worked to date and I realized that about the chr(34)... thank you.
0
 
Rey Obrero (Capricorn1)Commented:
try this

LIKE " & Chr(34) & "*" & val3 & "*" & Chr(34) & ""


0
 
GRayLCommented:
v="gone"
docmd.RunSQL "Delete from absameline1 where f like ""*" & v & "*""

How were we to know 'nothing has worked'?  Anyway, I just ran the above code again from the Immediate Pane and it deleted the one line in my table where f = "gone".  What can I say.
0
 
GRayLCommented:
Notice those are all double quotes - no single quotes.
0
 
Rey Obrero (Capricorn1)Commented:


strsql="select * from TableName"
strsql=strsql & " where [name] like  " & Chr(34) & "*" & val3 & "*" & Chr(34) & ""


0
 
ellenjbrAuthor Commented:
Hi everyone!

This is what I got to work ... thank you for all of your input and time ....
Dim str, val1 As String

val1 = "f"
val1 = val1 + "*"

str = "Delete Table1.field1 FROM Table1 WHERE Table1.field1 Like  '" & val1 & "' ;"

DoCmd.RunSQL (str)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.