Show all records if parameter on form is empty

[Equine Investor]

Hi guys,

I've searched everywhere for this one, so any help appreciated.

I have a form with minimum and maximum parameters for a query and if EITHER of the minimum or maximum fields or both of them are left blank, then it should return all records.

Here is my criteria:

Between [Forms]![SEARCH]![TABMin] And [Forms]![SEARCH]![TABMax]

Now what I need is if either TABMin or TABMax or both are left blank that the query returns all records!

I've found some solutions, but not incorporating the two parameters - only one.

Any gurus help please?

 

[Joe4]

Can you program it so:

If [Forms]![SEARCH]![TABMin] is blank, set it equal to #01/01/1900#
If [Forms]![SEARCH]![TABMax] is blank set it equal to #12/31/2099#

or something of the sort?

If you haven't already done it, it will probably require VBA instead of just trying to do a parameter query.

 

[Equine Investor]

Thanks for the reply jmiskey.

No, it would not achieve the objective.

I see what your solution is trying to do, but I need to incorporate all of it into the BETWEEN and AND statements.

"Between [Forms]![SEARCH]![TABMin] And [Forms]![SEARCH]![TABMax]"

I'm sure it can be expressed, but need some help in building the statement.

Here is my literal statement without the code that I need:

If [Forms]![SEARCH]![TABMin]="" and/or [Forms]![SEARCH]![TABMax]="" then show all records, otherwise show Between [Forms]![SEARCH]![TABMin] And [Forms]![SEARCH]![TABMax]

I just can't get the expression right???

 

[Equine Investor]

SOLUTION FOR ANYONE SEARCHING LATER....

between Nz([Forms]![SEARCH]![TABMin],0)
and Nz([Forms]![SEARCH]![TABMax],999999)

BUT - my problem was that not all records were populated by a TAB value, therefore it only showed those records where a value lay rather than all records.

The solution was to populate the field TAB with 0 values where the value was empty.