Help with "Like" syntax in query criteria

[Joe M.]

I have a query which gets its parameters from a form. I want to use "like" in
a query. So far I havent figured it out. I want the user to enter in a
partial string and have all of the records returned which contain it.
Also for fields on the form which are left blank I need the syntax for
returning results when the field is null. So 2 syntaxes I need:
1) for Like with data from a form
2) for returning results when field = NULL

Many thanks,
Joe M.

 

[Paul Shapiro]

If you use parameters in your query, then your code assigns the parameter
values from the data on your search form. The Where clause in that case
might be something similar to:
Where (@city Is Null Or city Like @city + "*") And (... similar for other
fields)

using @city as the parameter and city as the field name. If you're building
the sql in code, not paramterized, it would be the same idea but different
implementation.

 

[Ken Snell MVP]

SELECT *
FROM YourTableName
WHERE FieldName Like "*" & [Forms]![NameOfYourForm]![NameOfControlOnForm] &
"*" OR Len([Forms]![NameOfYourForm]![NameOfControlOnForm] & "") = 0;

 

[Joe M.]

Ken,
Is this syntax meant to be entered in the criteria section underneath the
field column in the query? I copied and paste but got a syntax error when I
tried to save.
Thanks,
Joe M.

SELECT *
FROM YourTableName
WHERE FieldName Like "*" & [Forms]![NameOfYourForm]![NameOfControlOnForm] &
"*" OR Len([Forms]![NameOfYourForm]![NameOfControlOnForm] & "") = 0;

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

I have a query which gets its parameters from a form. I want to use "like"
in
a query. So far I havent figured it out. I want the user to enter in a
partial string and have all of the records returned which contain it.
Also for fields on the form which are left blank I need the syntax for
returning results when the field is null. So 2 syntaxes I need:
1) for Like with data from a form
2) for returning results when field = NULL

Many thanks,
Joe M.

 

[John W. Vinson]

Ken,
Is this syntax meant to be entered in the criteria section underneath the
field column in the query? I copied and paste but got a syntax error when I
tried to save.
Thanks,
Joe M.

SELECT *
FROM YourTableName
WHERE FieldName Like "*" & [Forms]![NameOfYourForm]![NameOfControlOnForm] &
"*" OR Len([Forms]![NameOfYourForm]![NameOfControlOnForm] & "") = 0;

PMFJI but... this is *the entire query*. The query design grid is just a tool
to build SQL strings like this.

You can go into the SQL view of a query, and copy and paste Ken's SQL; change
the name of your table, your field, your form and the name of the control on
that form to match your own database.

Or, just put

Like "*" & [Forms]![NameOfYourForm]![NameOfControlOnForm] & "*" OR
Len([Forms]![NameOfYourForm]![NameOfControlOnForm] & "") = 0

in the Criteria box on the query grid, again changing to the actual names of
your form and control.

 

[Ken Snell MVP]

PMFJI but... this is *the entire query*. The query design grid is just a
tool
to build SQL strings like this.

No problem from me on your JI!