If Null Show All Records

[valntyn]

Here is my setup.

I have two tables "Search" and "Plans."
The "Search" table consists of the fields County, Route, Begin, and End.
The "Plans" table consists of the fields County, Route, Begin, End, and
Comments.
A form with two linked combo boxes and two text boxes imputes the values in
the "Search" table.
You select the County from the County combo, then a Route from the Route
combo. Numeric values are entered in the text boxes for Begin and End.
A query finds all records in the "Plans" table that matches the County, Route,
and any Begin field values that are between the values in the Begin and End
fields from the "Search" table.
The code in the Criteria under the "Plans" Begin field of the query is:

Between [Search]![Begin] And [Search]![End]

What I would like now is the ability to leave the Begin and End field values
blank in the "Search" table and have the query return all records that match
based on just the County and Route field values.

Hope this makes some sense. Thanks for the help.

 

[Guest]

I did not follow all but if you are using a form to make selections the your
criteria needs to reflect the form and not the table like this --
Between [Forms]![MySearch]![Begin] And [Forms]![MySearch]![End]

Try the above first to make sure it is reading the form.
Like IIf([Forms]![MySearch]![Begin] Is Null,"*") Or Between
[Forms]![MySearch]![Begin] And [Forms]![MySearch]![End]

 

[valntyn via AccessMonster.com]

That works great, except that some of the records in the "Plans" table have a
Null value for the Begin field. It still doesn't show those records in the
query.

What should I do next?

Thanks in advance!