Syntax for SQL with Like & Wildcard

[sumdumgai]

Trying to construct an sql string that will be used in VBA to select records from a table. I cannot figure how to use the wildcard character. Compiler does not like "*" and converts to " * ". Have also tried "'*" (double, single, asterisk, double), but can't get around compiler treating ' as comment start.
Can someone please correct the syntax below for a multi-parameter SQL string with 'Like' command?

strSql = "SELECT [t1].f1, [t1].f2, [t1].f3, [t1].f4, [t1].f5, [t1].f6, [t1].f7 " _
    & "FROM [t1] " _
    & "WHERE ((([t1].f1) Like " * " & [forms]![SearchF]![f1] & " * ") " _
    & "AND (([t1].f2) Like " * " & [forms]![SearchF]![f2] & " * ") " _
    & "AND (([t1].f3) Like " * " & [forms]![SearchF]![f3] & " * ") " _
    & "AND (([t1].f4) Like " * " & [forms]![SearchF]![f4] & " * ") " _
    & "AND (([t1].f5) Like " * " & [forms]![SearchF]![f5] & " * ") " _
    & "AND (([t1].f6) Like " * " & [forms]![SearchF]![f6] & " * "));"

Thanks.

 

[JHB]

When you search for text values, then surround it with ' '.
When number values the without ' '.

strSql = "SELECT [t1].f1, [t1].f2, [t1].f3, [t1].f4, [t1].f5, [t1].f6, [t1].f7 " _
    & "FROM [t1] " _
    & "WHERE [t1].f1 Like '*" & [Forms]![SearchF]![f1] & "*' " _
    & "AND [t1].f2 Like '*" & [Forms]![SearchF]![f2] & "*' " _
    & "AND [t1].f3 Like '*" & [Forms]![SearchF]![f3] & "*' " _
    & "AND [t1].f4 Like '*" & [Forms]![SearchF]![f4] & "*' " _
    & "AND [t1].f5 Like '*" & [Forms]![SearchF]![f5] & "*' " _
    & "AND [t1].f6 Like '*" & [Forms]![SearchF]![f6] & "*';"

If you create the string in the form "Search" then use Me. instead of [Forms]![SearchF].

strSql = "SELECT [t1].f1, [t1].f2, [t1].f3, [t1].f4, [t1].f5, [t1].f6, [t1].f7 " _
    & "FROM [t1] " _
    & "WHERE [t1].f1 Like '*" & Me.[f1] & "*' " _
    & "AND [t1].f2 Like '*" & Me.[f2] & "*' " _
    & "AND [t1].f3 Like '*" & Me.[f3] & "*' " _
    & "AND [t1].f4 Like '*" & Me.[f4] & "*' " _
    & "AND [t1].f5 Like '*" & Me.[f5] & "*' " _
    & "AND [t1].f6 Like '*" & Me.[f6] & "*';"

 

[arnelgp]

remove the extra ) from the expression:

strSql = "SELECT [t1].f1, [t1].f2, [t1].f3, [t1].f4, [t1].f5, [t1].f6, [t1].f7 " _
    & "FROM [t1] " _
    & "WHERE [t1].f1 Like '*" & Me.[f1] & "*' " _
    & "AND [t1].f2 Like '*" & Me.[f2] & "*' " _
    & "AND [t1].f3 Like '*" & Me.[f3] & "*' " _
    & "AND [t1].f4 Like '*" & Me.[f4] & "*' " _
    & "AND [t1].f5 Like '*" & Me.[f5] & "*' " _
    & "AND [t1].f6 Like '*" & Me.[f6] & "*';"

 

[sumdumgai]

Thanks for your help. No compiler errors, but error 3061, too few parameters, expect 6, with both [Forms]![SearchF]![f1] and Me.[f1].

 

[arnelgp]

check the correct Textbox name for f1 on your form (property).

 

[isladogs]

Is t1 the name of a table or its alias? Similarly f1,f2,f3 etc?
Or are you just 'simplifying for the purpose of this thread?

Try this instead:

strSQL = "SELECT [t1].f1, [t1].f2, [t1].f3, [t1].f4, [t1].f5, [t1].f6, [t1].f7" & _
     " FROM [t1]" & _
     " WHERE [t1].f1 Like ""*" & Me.[f1] & "*""" & _
     " AND [t1].f2 Like ""*" & Me.[f2] & "*""" & _
     " AND [t1].f3 Like ""*" & Me.[f3] & "*""" & _
     " AND [t1].f4 Like ""*" & Me.[f4] & "*""" & _
     " AND [t1].f5 Like ""*" & Me.[f5] & "*""" & _
     " AND [t1].f6 Like ""*" & Me.[f6] & "*"";"

If it works it is likely to be VERY SLOW due to all the wildcards.
A better approach is explained by Allen Browne here: http://allenbrowne.com/ser-62.html

 

[sumdumgai]

Thanks.

t1, f1 simplified for thread.

Tried your suggestion on adding dq's and got compiler syntax error.


Also checked for correct textbox name.

 

[arnelgp]

You missed f7:

strSql = "SELECT [t1].f1, [t1].f2, [t1].f3, [t1].f4, [t1].f5, [t1].f6, [t1].f7 " _
    & "FROM [t1] " _
    & "WHERE [t1].f1 Like '*" & Me.[f1] & "*' " _
    & "AND [t1].f2 Like '*" & Me.[f2] & "*' " _
    & "AND [t1].f3 Like '*" & Me.[f3] & "*' " _
    & "AND [t1].f4 Like '*" & Me.[f4] & "*' " _
    & "AND [t1].f5 Like '*" & Me.[f5] & "*' " _
    & "AND [t1].f6 Like '*" & Me.[f6] & "*' " _
[COLOR="Blue"]    & "AND [t1].f7 Like '*" & Me.[f7] & "*';"[/COLOR]

is [COLOR="Blue"]t1[/COLOR] the real table name? if not substitute the correct table name and not the Alias.

 

[sumdumgai]

Changed syntax to:
Like '*" & Me.[DEA] & "*' "
and fixed syntax and missing parameter errors but :
Set rs = db.OpenRecordset(strSql)
returns nothing to screen.

 

[sumdumgai]

Thanks arnegp, but I don't want to set criteria for f7.


The t1, f1 are names I'm using in this thread. My SQL uses the actual table names.

 

[arnelgp]

it will not show on screen, since it is a recordset.
you need first to create a Query with all or some of the fields that you showed.


from your code:

strSQL = "SELECT [t1].f1, [t1].f2, [t1].f3, [t1].f4, [t1].f5, [t1].f6, [t1].f7" & _
" FROM [t1]" & _
" WHERE [t1].f1 Like ""*" & Me.[dea] & "*""" & _
" AND [t1].f2 Like ""*" & Me.[f2] & "*""" & _
" AND [t1].f3 Like ""*" & Me.[f3] & "*""" & _
" AND [t1].f4 Like ""*" & Me.[f4] & "*""" & _
" AND [t1].f5 Like ""*" & Me.[f5] & "*""" & _
" AND [t1].f6 Like ""*" & Me.[f6] & "*"";"

with currentdb.querydefs("theQueryYouMade")
.sql = strSQL
end with
docmd.openQuery "theQueryYouMade"

or there is a subForm, use its RecordSource:

Me.SubformName.Form.RecordSource=strSQL

 

[sumdumgai]

Thank you so much. I think I have it working now.