I have a form/subform whcih is designed to mimic the filtering
capabilities of excel using combo boxes. The form has the combo boxes
and the subform is a datasheet set to return the results of a query
based on the criteria selected in the combo boxes on the form. In
order to do this, I have set the criteria of each field in the query
to be the value inputted (i.e., Criteria: [Forms]![MainForm]!
[combobox]). The problem I am having is if there are null values in
any field of the query, I lose that record from the unfiltered query.
Any suggestions on how I can resolve this issue?
Not sure what you're asking, Uninvisible. If a field in the table is NULLdo
you want it to be retrieved regardless of the criteria? Or do you just want to
allow for NULL criteria (retrieving all records if you don't specify)? Ifso,
use a criterion of
= [Forms]![MainForm]![combobox] OR [Forms]![MainForm]![combobox] IS NULL
Thank you for the reply John. The issue I am facing is as follows.
If I were to write the query without any criteria, it runs as expected
without issue. When I put the [Forms]![MainForm]![combobox] criteria
in the query, if it is on a field where all records have a value
populated for the field, then there is no problem. I can run the
query and after selecting "OK" to the warning message, the query runs
with all expected records showing. The issue arises when I put the
criteria on a field where some or none of the records do not have a
value populated to that field. In this instance, the query produces
no results. To illustarte. If I query three fields without any
criteria, I get all 4 records in the table being queried:
[Field 1] [Field 2] [Field 3]
1 Data Data Null
2 Data Data Null
3 Data Data Null
4 Data Data Null
If I put my criteria of [Forms]![MainForm]![combobox] on Field 1 and
run the query, after ok'ing the warning messages, the query populates
all 4 records; however, when I also put the criteria on Field 3, the
query yields no records on account of them all being null. Putting in
an Or Is Null statment doesn't help because when I use the combo boxes
to filter the subform, the filter results in returning the records
that meet the value in the combobox as well as records that have a
null value. As such, I need a way to be able to return only records
that meet the criteria and exclude those that have null records.
