Solved

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

Posted on 2007-11-30
21
599 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
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
 
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

912 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

22 Experts available now in Live!

Get 1:1 Help Now