asked on

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!

Ryan Chong

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,"'","''") & "%'"

Shiju S

Try this
'-----------------------------------------------
strSQL = "Field1 LIKE '%" & Replace(txtOption1,"'","''") & "%' AND Field2 LIKE '%" & Replace(txtOption2,"'","''") & "%' AND Field3 LIKE '%" & Replace(txtOption3,"'","''") & "%'"
'------------------------------------------------

u put an extra & instead of %

;-)
Shiju

Shiju S

hi

sorry i am late
ryancys deserves it
;-)
Shiju

APlusComp247

ASKER

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??

ASKER CERTIFIED SOLUTION

JR2003

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JR2003

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

JR2003

oops
Please ignore the last comment, wrong question answered!
JR

Ark

Try to put fields name into a brackets :
[Field1] Like ...

APlusComp247

ASKER

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!!