Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 611
  • Last Modified:

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.
0
ellenjbr
Asked:
ellenjbr
  • 7
  • 4
  • 4
  • +4
1 Solution
 
Patrick MatthewsCommented:
Hello ellenjbr,

Please post a more complete snippet of the code.

Regards,

Patrick
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
You cannot use a string variable in a query expression.

mx
0
 
ellenjbrAuthor Commented:
Since when ?
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
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
 
Computer101Commented:
PAQed with points refunded (500)

Computer101
EE Admin
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 4
  • 4
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now