Solved

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

Posted on 2007-11-30
21
598 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
  • 7
  • 4
  • 4
  • +4
21 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
Hello ellenjbr,

Please post a more complete snippet of the code.

Regards,

Patrick
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
You cannot use a string variable in a query expression.

mx
0
 

Author Comment

by:ellenjbr
Comment Utility
Since when ?
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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
Comment Utility
This worked in the immediate pane:

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

Expert Comment

by:GRayL
Comment Utility
TABLENAME.[TABLENAME Name]) Like ""*" & val3 & "*""

that work?
0
 

Author Comment

by:ellenjbr
Comment Utility
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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Have you tried any of the above posts ?

mx
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
I just showed you.
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
TABLENAME.[TABLENAME Name]) Like ""*" & val3 & "*""

that work?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 44

Expert Comment

by:GRayL
Comment Utility
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
Comment Utility
(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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
ellen ... note that Chr(34) is a double quote character.  I use Chr(34) for clarity of posting.

mx
0
 

Author Comment

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

Expert Comment

by:Rey Obrero
Comment Utility
try this

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


0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
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
Comment Utility
Notice those are all double quotes - no single quotes.
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility


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


0
 

Author Comment

by:ellenjbr
Comment Utility
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
Comment Utility
PAQed with points refunded (500)

Computer101
EE Admin
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

771 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now