VB6 Find Problem using SQL(MS Access 2007)

Question

I had a program that will find a record from a database(MS Access 2007) where im havin an error: Syntax Error in query expression 'firstname like %1%'

this is my code:

 Dim find As String

 find = txtfind.Text

 If txtfind.Text <> "" Then
    Set rs = db.OpenRecordset("SELECT * from records WHERE firstname like '*'" & find & "'*")

    If rs.EOF = True Then
        MsgBox "No Record Found!", vbCritical + vbOKOnly, "Error"
    Else
        Text1 = rs(0)
        Text2 = rs(1)
        Text3 = rs(2)
        Text4 = rs(3)
    End If

    If Not rs Is Nothing Then
       Set rs = Nothing
    Else
       rs.Close
    End If

End If

This: `"SELECT * from records WHERE firstname like '*'" & find & "'*"` will produce `SELECT * from records WHERE firstname like '*'1'`. I believe it should be `"SELECT * from records WHERE firstname like '*" & find & "*'"` (notice that I removed one **'** and changed position of another). — ssarabando, Apr 06 '11 at 09:31
Are you guarded against SQL injection in your `WHERE` clause? See http://stackoverflow.com/questions/512174/non-web-sql-injection/515150#515150 — onedaywhen, Apr 06 '11 at 15:08

Vaibhav · Answer 1 · 2011-04-06T09:30:51.057

0

The Wildcard character for Access is * not %.

Please try with the following:

Set rs = db.OpenRecordset("SELECT * from records WHERE firstname like '*" & find & "*'")

Also, a comprehensive resource for using wildcard characters in access:

10 tips for using wildcard characters in Microsoft Access

Hope that helps!

edited Apr 06 '11 at 09:30

answered Apr 06 '11 at 09:19

Vaibhav

1,156
2
12
27

how about this code i edited? i'm having an error regarding the syntax of the code... – Andro Miguel M. Bondoc Apr 06 '11 at 09:26
Updated the original answer. Hope that helps. Try this: Set rs = db.OpenRecordset("SELECT * from records WHERE firstname like '\*" & find & "\*'") The single quote should appear before the first \* character and after the second \* character, not in between. – Vaibhav Apr 06 '11 at 09:31
Read your "resource" to discover that "The Wildcard character for Access is * not %" is a misstatement. Comprehensive resource? It doesn't mention the `ALIKE` keyword. See http://stackoverflow.com/questions/5166907/ms-access-sql-any-reason-why-like-does-not-work/5170214#5170214 Also, I couldn't see mention of the fact that since Access2003 the UI can be placed in ANSI-92 Query Mode. – onedaywhen Apr 06 '11 at 15:05

VB6 Find Problem using SQL(MS Access 2007)

1 Answers1