How to find specific value or return all records

[Emily T]

How can I set up my criteria so that if a user does not enter a value in the
form, all records are returned?

For example my form allows a user to search by country. But if the country
text box is left blank no records are returned (which I don't want to happen).

Thank you.

 

[Ken Sheridan]

Test for OR <parameter> IS NULL. In query design view you'd out something
like this in the criteria row of the Country column:

Forms![YourForm]![txtCountry] OR Forms![YourForm]![txtCountry] IS NULL

In SQL it looks like this:

SELECT *
FROM Customers
WHERE (Country = Forms![YourForm]![txtCountry]
OR Forms![YourForm]![txtCountry] IS NULL);

If the user enters a country name in the parameter then the first half of
the expression will evaluate to TRUE for each row with the relevant country
name, the second half will evaluate to FALSE because he parameter isn't Null.
As an OR operation requires only one part to be TRUE then the row will be
returned. For rows with a different country name both parts will evaluate to
FALSE so those rows won't be returned.

If no value is entered in the parameter then the second half of the
expression will evaluate to TRUE for every row regardless of the country
name, so all rows will be returned.

You'll sometimes see it recommended that you use:

LIKE Forms![YourForm]![txtCountry] & "*".

However, this will not return rows where the Country column is NULL, so
unless Nulls are disallowed in the Country column, i.e. it’s a 'Required'
column, not all rows will necessarily be returned if no value is entered in
the parameter. The above method is more reliable therefore. If you want to
see the difference in results try both methods on the Region column in the
Customers table in the sample Northwind database which comes with Access.

BTW if you save the query in design view and then reopen it in design view
you'll find that Access has moved things around. Don't worry it will work
just the same.

Ken Sheridan
Stafford, England

 

[Emily T]

Thank you very much for the helpful reply. That does work.
In an attempt to use a wild card feature I modified your suggestion to the
following:
Like "*" & [Forms]![Country form]![Country text] & "*"

Based on your note about the "LIKE" feature below, is there still a danger
that the above code will not capture all records???

Thanks!

Test for OR <parameter> IS NULL. In query design view you'd out something
like this in the criteria row of the Country column:

Forms![YourForm]![txtCountry] OR Forms![YourForm]![txtCountry] IS NULL

In SQL it looks like this:

SELECT *
FROM Customers
WHERE (Country = Forms![YourForm]![txtCountry]
OR Forms![YourForm]![txtCountry] IS NULL);

If the user enters a country name in the parameter then the first half of
the expression will evaluate to TRUE for each row with the relevant country
name, the second half will evaluate to FALSE because he parameter isn't Null.
As an OR operation requires only one part to be TRUE then the row will be
returned. For rows with a different country name both parts will evaluate to
FALSE so those rows won't be returned.

If no value is entered in the parameter then the second half of the
expression will evaluate to TRUE for every row regardless of the country
name, so all rows will be returned.

You'll sometimes see it recommended that you use:

LIKE Forms![YourForm]![txtCountry] & "*".

However, this will not return rows where the Country column is NULL, so
unless Nulls are disallowed in the Country column, i.e. it’s a 'Required'
column, not all rows will necessarily be returned if no value is entered in
the parameter. The above method is more reliable therefore. If you want to
see the difference in results try both methods on the Region column in the
Customers table in the sample Northwind database which comes with Access.

BTW if you save the query in design view and then reopen it in design view
you'll find that Access has moved things around. Don't worry it will work
just the same.

Ken Sheridan
Stafford, England

How can I set up my criteria so that if a user does not enter a value in the
form, all records are returned?

For example my form allows a user to search by country. But if the country
text box is left blank no records are returned (which I don't want to happen).

Thank you.

 

[Ken Sheridan]

If the Country column allows Nulls, yes. If you use the LIKE operator rows
with Null in the Country column will not be returned whatever you use in the
way of wildcards. This is because a Null is not a value but the absence of a
value, an 'unknown', and when Null is compared with anything the result is
always Null, not True or False. This is even so if Null is compared with
Null. This is why IS NULL or IS NOT NULL has to be used. There is really no
point at all in using the LIKE operator if you want all rows returned,
including those with Nulls; using '[Forms]![Country form]![Country text] OR
[Forms]![Country form]![Country text] IS NULL' is the safe option.

Another problem with the Like operator and wildcards is that because it is
designed for pattern searching, you might find that it will return unwanted
rows if a value is entered in the parameter. I can't immediately think of
any countries where this might happen, but in the case of regions it could
well do so. If Hampshire (in England) were entered then rows for the US
state of New Hampshire would also be returned if the two wildcards were used.

Ken Sheridan
Stafford, England

Thank you very much for the helpful reply. That does work.
In an attempt to use a wild card feature I modified your suggestion to the
following:
Like "*" & [Forms]![Country form]![Country text] & "*"

Based on your note about the "LIKE" feature below, is there still a danger
that the above code will not capture all records???

Thanks!

Test for OR <parameter> IS NULL. In query design view you'd out something
like this in the criteria row of the Country column:

Forms![YourForm]![txtCountry] OR Forms![YourForm]![txtCountry] IS NULL

In SQL it looks like this:

SELECT *
FROM Customers
WHERE (Country = Forms![YourForm]![txtCountry]
OR Forms![YourForm]![txtCountry] IS NULL);

If the user enters a country name in the parameter then the first half of
the expression will evaluate to TRUE for each row with the relevant country
name, the second half will evaluate to FALSE because he parameter isn't Null.
As an OR operation requires only one part to be TRUE then the row will be
returned. For rows with a different country name both parts will evaluate to
FALSE so those rows won't be returned.

If no value is entered in the parameter then the second half of the
expression will evaluate to TRUE for every row regardless of the country
name, so all rows will be returned.

You'll sometimes see it recommended that you use:

LIKE Forms![YourForm]![txtCountry] & "*".

However, this will not return rows where the Country column is NULL, so
unless Nulls are disallowed in the Country column, i.e. it’s a 'Required'
column, not all rows will necessarily be returned if no value is entered in
the parameter. The above method is more reliable therefore. If you want to
see the difference in results try both methods on the Region column in the
Customers table in the sample Northwind database which comes with Access.

BTW if you save the query in design view and then reopen it in design view
you'll find that Access has moved things around. Don't worry it will work
just the same.

Ken Sheridan
Stafford, England

How can I set up my criteria so that if a user does not enter a value in the
form, all records are returned?

For example my form allows a user to search by country. But if the country
text box is left blank no records are returned (which I don't want to happen).

Thank you.