Syntax for SQL with Like & Wildcard (1 Viewer)

S

sumdumgai

Registered User.
Local time
Today, 02:07
Joined
Jul 19, 2007
Messages
453
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?



Code: 
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.
 
J

JHB

Have been here a while
Local time
Today, 08:07
Joined
Jun 17, 2012
Messages
7,732
When you search for text values, then surround it with ' '.
When number values the without ' '.
Code: 
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].
Code: 
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] & "*';"
 
Last edited:
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:07
Joined
May 7, 2009
Messages
19,230
remove the extra ) from the expression:
Code: 
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] & "*';"
 
S

sumdumgai

Registered User.
Local time
Today, 02:07
Joined
Jul 19, 2007
Messages
453
Thanks for your help. No compiler errors, but error 3061, too few parameters, expect 6, with both [Forms]![SearchF]![f1] and Me.[f1].
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:07
Joined
May 7, 2009
Messages
19,230
check the correct Textbox name for f1 on your form (property).
 
isladogs

isladogs

MVP / VIP
Local time
Today, 07:07
Joined
Jan 14, 2017
Messages
18,209
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:

Code: 
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
 
S

sumdumgai

Registered User.
Local time
Today, 02:07
Joined
Jul 19, 2007
Messages
453
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

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:07
Joined
May 7, 2009
Messages
19,230
You missed f7:
Code: 
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.
 
S

sumdumgai

Registered User.
Local time
Today, 02:07
Joined
Jul 19, 2007
Messages
453
Changed syntax to:
Like '*" & Me.[DEA] & "*' "
and fixed syntax and missing parameter errors but :
Set rs = db.OpenRecordset(strSql)
returns nothing to screen.
 
S

sumdumgai

Registered User.
Local time
Today, 02:07
Joined
Jul 19, 2007
Messages
453
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

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:07
Joined
May 7, 2009
Messages
19,230
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
 
You must log in or register to reply here.

Similar threads

ahmedjamalaboelez
Solved F Keys Functions for each object in form
Replies
2
Views
683
The_Doc_Man
The_Doc_Man
E
Solved Listbox search not working with aggregated query
Replies
7
Views
256
elannesse
E
C
Solved Error in sql code
Replies
8
Views
1,045
chizzy42
C
B
Solved Problems exporting text file
Replies
14
Views
1,621
CarlettoFed
C
S
Solved System Resources Exceeded - This query
Replies
14
Views
2,430
sumdumgai
S

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom