Solved

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

Posted on 2007-11-30
21
603 Views
Last Modified: 2010-05-18
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
Comment
Question by:ellenjbr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 4
  • +4
21 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 20384223
Hello ellenjbr,

Please post a more complete snippet of the code.

Regards,

Patrick
0
 
LVL 75
ID: 20384249
You cannot use a string variable in a query expression.

mx
0
 

Author Comment

by:ellenjbr
ID: 20384267
Since when ?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75
ID: 20384300
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
 
LVL 44

Expert Comment

by:GRayL
ID: 20384600
This worked in the immediate pane:

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

Expert Comment

by:GRayL
ID: 20384618
TABLENAME.[TABLENAME Name]) Like ""*" & val3 & "*""

that work?
0
 

Author Comment

by:ellenjbr
ID: 20384683
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
 
LVL 75
ID: 20384715
Have you tried any of the above posts ?

mx
0
 
LVL 44

Expert Comment

by:GRayL
ID: 20384738
I just showed you.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 20384771
TABLENAME.[TABLENAME Name]) Like ""*" & val3 & "*""

that work?
0
 
LVL 44

Expert Comment

by:GRayL
ID: 20384790
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
 
LVL 38

Expert Comment

by:PaulHews
ID: 20384955
(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
 
LVL 75
ID: 20384976
ellen ... note that Chr(34) is a double quote character.  I use Chr(34) for clarity of posting.

mx
0
 

Author Comment

by:ellenjbr
ID: 20385187
DatabaseMx - Yes, and nothing has worked to date and I realized that about the chr(34)... thank you.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20385229
try this

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


0
 
LVL 44

Expert Comment

by:GRayL
ID: 20385232
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
 
LVL 44

Expert Comment

by:GRayL
ID: 20385236
Notice those are all double quotes - no single quotes.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20385243


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


0
 

Author Comment

by:ellenjbr
ID: 20385265
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
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 21186180
PAQed with points refunded (500)

Computer101
EE Admin
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question