retaining null values when filtering with a combo box

[Paul Ponzelli]

I have two combo boxes on a form, and I'm using one to select records in the
form, and the other as the criteria in the Row Source property of the first
combo box. In other words, I'm using a combo box to filter the record
source of the "selection" combo box.

That "filter" combo box lists attorneys, so when you select a given
attorney, the only records displayed in the Row Source of the "selection"
combo box are those corresponding to the selected attorney. One of the
selections is "All Attorneys" and the bound column has the value "*"
(asterisk) when that selection is made. The criteria of the selection combo
box is

Like forms!formName!cboAttorneyFilter

so that when "All Attorneys" ("*") is selected, the records for all
attorneys are displayed in the selection combo box. When a specific
attorney is selected, only the records for that attorney are displayed.

This all works as intended except for one thing - while "All Attorneys" does
indeed display records for all attorneys, it only displays records where the
Attorney field is not null. What I would like to do is modify the selection
criteria so that when "All Attorneys" is selected in the filter combo box,
the Row Source of the selection combo box will display all records,
including Null values. However, I don't want those null values to be
displayed when a particular attorney is selected.

How can I modify my selection criteria to include Null values when "All
Attorneys" ("*") is selected?

Thanks in advance,

Paul

 

[Rick B]

I believe it would be...

Like forms!formName!cboAttorneyFilter & *

 

[Paul Ponzelli]

Thank you for your reply, Rick, however, the solution you suggested doesn't
have any effect on the selection combo box. It still displays only records
that have non-null values in the Attorney field when I select "All
Attorneys" in the "filter" combo box.

So again, my question to the group - how can I modify the criteria in the
Attorney field so that it will display all records, including those with
Null values in the Attorney field, when I select All Attorneys in the filter
combo box?

Thanks again in advance,

Paul

 

[Allen Browne]

You are correct, Paul.
Like "*" does not return nulls.

It is possible to craft the WHERE clause so the value is True when the combo
is null, e.g.:
((Forms!formName!cboAttorneyFilter Is Null)
OR ([Attorney] = Forms!formName!cboAttorneyFilter))

If you are mixing that with other criteria, watch the bracketing.

If you have several of these filter boxes, a better technique might be to
build the filter string from only those boxes that that have a value instead
of the convoluted approach above. Details in:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

 

[Paul Ponzelli]

I tried what you suggested, Allen, as well as a couple of syntactical
variations, and I couldn't get it to return the null values in addition to
the value selected in the combo box. It only returns records corresponding
to the value selected in the combo box.

The workaround I decided to use is to substitute dummy values in place of
the null values for attorney - "na" - as in "not available" and then it
becomes something I can work with.

But thank you for your suggestion; I'm glad to know you're still out there.
I'm still using information from your web site

http://allenbrowne.com/tips.html

on an ongoing basis, and it's been helpful on many occasions.

Paul

 

[Allen Browne]

If you put the expression into the Criteria row of the query, it won't work.
You need to type it directly into the WHERE clause, so your query reads:
SELECT ...
FROM ...
WHERE ((Forms!formName!cboAttorneyFilter Is Null)
OR ([Attorney] = Forms!formName!cboAttorneyFilter));

The WHERE clause is something that Access evaluates to True (choose the
record) or False (reject the record) for every record it considers. The
expression above can be true in either of 2 ways:
a) if the combo is null, the first part is True;
b) if the combo matched the Attorney field, the 2nd part is True.
An OR is true when either part is true.
Therefore the expression is True for all records when the combo is null.
So, when the combo is null, all records are chosen, and if the combo is not
null, only the records that match are chosen.

 

[Paul Ponzelli]

You're right - it works.

Thanks for the clarification, Allen.

Paul