Solved

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

Posted on 2007-11-30
21
606 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Linux Academy Android App Now Supports Chromecast

We have some fantastic news for our Android fans. We’re so excited to announce that the Linux Academy Android app is now available with Chromecast support. That’s right – simply download the latest update of the Linux Academy App and start casting your favorite course videos!

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses
Course of the Month9 days, 13 hours left to enroll

624 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