Improve company productivity with a Business Account.Sign Up

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

SQL - LIKE AND

I hope this is an easy question for someone.  I am using ADO to access some data.  I need to write a SQL statement to narrow that data down.  I can use the LIKE statement successfully for one option, but not multiple.  Here is some code.

'This string will work
strSQL = "Field1 LIKE '%" & txtOption1 & "%'"

'I need something like this to work
strSQL = "Field1 LIKE '%" & txtOption1 & "&' AND Field2 LIKE '%" & txtOption2 & "%' AND Field3 LIKE '%" & txtOption3 & "%'"

Set rsRecordSet = New ADODB.Recordset
With rsRecordSet
    .CursorType = adOpenStatic
    .CursorLocation = adUseClient
    .LockType = adLockPessimistic
    .Source = "SELECT * FROM Table1 WHERE " & strSQL
    .ActiveConnection = connConnection
    .Open
End With

I get the follwoing error when I run it with the SQL statement that has multiple LIKE statements in it.  ( I put "test" in txtOption2)

**  Syntex error (missing operator) in query expression 'AND Field2 LIKE '%test%"  **

Thanks for the help!
0
APlusComp247
Asked:
APlusComp247
  • 3
  • 2
  • 2
  • +2
1 Solution
 
Ryan ChongCommented:
try:

strSQL = "Field1 LIKE '%" & replace(txtOption1,"'","''") & "' AND Field2 LIKE '%" & replace(txtOption2,"'","''") & "%' AND Field3 LIKE '%" & replace(txtOption3,"'","''") & "%'"

or

strSQL = "Field1 LIKE '%" & replace(txtOption1,"'","''") & "%' AND Field2 LIKE '%" & replace(txtOption2,"'","''") & "%' AND Field3 LIKE '%" & replace(txtOption3,"'","''") & "%'"

0
 
Shiju SasidharanAssoc Project ManagerCommented:
Try this
'-----------------------------------------------
strSQL = "Field1 LIKE '%" & Replace(txtOption1,"'","''") & "%' AND Field2 LIKE '%" & Replace(txtOption2,"'","''") & "%' AND Field3 LIKE '%" & Replace(txtOption3,"'","''")  & "%'"
'------------------------------------------------

u put an extra   &   instead of   %

;-)
Shiju

0
 
Shiju SasidharanAssoc Project ManagerCommented:
hi

sorry i am late
ryancys  deserves it
;-)
Shiju


0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

 
APlusComp247Author Commented:
I understand what you are getting at, but I haven't used any apostraphes in the textboxes so the Replace statement shouldn't change anything.... right?

I tried that and it didn't work.

Any more ideas??
0
 
JR2003Commented:
What is in the value of strSQL when you execute the command?  Can you paste it in here so we can have a look?
Just add the line :
Debug.Print "SELECT * FROM Table1 WHERE " & strSQL
when you set the '.Source' to make it appear in the immediate window.

Which database are you using?
0
 
JR2003Commented:
There is a very simple way to do this using the "Microsoft Script Control 1.0". All you need to do is create a string which is the expression you want to evaluate and just let the "Microsoft Script Control 1.0" evaluate it.

You can set the 'strExpression' string to any vb expression e.g.: "3 / 4" or "5 * (6 - 1)" etc.


Private Sub Command1_Click()
    Dim dblResult As Double
    Dim strExpression As String
    Dim MyScripControl As Object
    Set MyScripControl = CreateObject("ScriptControl")

    strExpression = "3 / 4"  '<---  You can put any expression you like here

    MyScripControl.Language = "VBScript"
    dblResult = MyScripControl.Eval(strExpression )
    MsgBox dblResult

End Sub
0
 
JR2003Commented:
oops
Please ignore the last comment, wrong question answered!
JR
0
 
ArkCommented:
Try to put fields name into a brackets :
[Field1] Like ...
0
 
APlusComp247Author Commented:
Ok, I made a stupid mistake.  I was using a few IF  statements to create the SQL statement depending on whether data was entered into the textbox or not.  I used the following

strSQL = " AND Field1 LIKE '%" & txtOption1 & "%'"

I quickly figured out after using the debug.print than I needed the following

strSQL = strSQL & " AND Field1 LIKE '%" & txtOption1 & "%'"

I was so unsure of my SQL syntax that I completely forgot a little simple debuging.

Thanks for all the help!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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