Like is Null Query from Form

[Guest]

I have a query with multiple criteria all coming from an unbound form. All
my criteria uses Like expressions, for example:

Like "*" & [Forms]![frmSearchCriteria]![Grantee] & "*"

When no criteria is entered in the form I need the query to return all
records. What it is doing is returning all records except ones with Null
values in any of the criteria fields. How do I tell it to still return the
record even if a field, [Grantee] is left blank, or Null?

Thanks.

 

[Guest]

Like "*" & [Forms]![frmSearchCriteria]![Grantee] & "*" Or Is Null

 

[Guest]

So simple. I swear I tried everything but that. Thanks a bunch!

Like "*" & [Forms]![frmSearchCriteria]![Grantee] & "*" Or Is Null

--
KARL DEWEY
Build a little - Test a little

I have a query with multiple criteria all coming from an unbound form. All
my criteria uses Like expressions, for example:

Like "*" & [Forms]![frmSearchCriteria]![Grantee] & "*"

When no criteria is entered in the form I need the query to return all
records. What it is doing is returning all records except ones with Null
values in any of the criteria fields. How do I tell it to still return the
record even if a field, [Grantee] is left blank, or Null?

Thanks.

 

[Allen Browne]

Actually, that returns the Nulls as well as the matches, even when you do
enter some criteria on the form.

If that's not what you want, you can change the WHERE clause of the query so
it looks like this:
WHERE (([Forms]![frmSearchCriteria]![Grantee] Is Null)
OR ([MyField] Like "*" & [Forms]![frmSearchCriteria]![Grantee] & "*"))
replacing MyField with your field name.

Comments:
========
a) Don't declare the parameter if MyField is a Text field:
http://allenbrowne.com/bug-13.html

b) Make sure you tab out of the Grantee control on your form before running
the query. (Otherwise its Value is not updated, and the query doesn't work
as expected.)

c) Watch the bracketing: if there are other phrases in the WHERE clause,
mixing ANDs and ORs.

d) If you have lots of fields like this to handle, this might be better:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

So simple. I swear I tried everything but that. Thanks a bunch!

Like "*" & [Forms]![frmSearchCriteria]![Grantee] & "*" Or Is Null

--
KARL DEWEY
Build a little - Test a little

I have a query with multiple criteria all coming from an unbound form.
All
my criteria uses Like expressions, for example:

Like "*" & [Forms]![frmSearchCriteria]![Grantee] & "*"

When no criteria is entered in the form I need the query to return all
records. What it is doing is returning all records except ones with
Null
values in any of the criteria fields. How do I tell it to still return
the
record even if a field, [Grantee] is left blank, or Null?