Show all records parameter query

[DrDance]

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'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've found some solutions, but not incorporating the two parameters - only one.

Any gurus help please?

 

[substitute]

Assiming TABMin and TABMax are integers try:

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

Use appropriate range limits in place of 0 and 999999

Ian.

 

[DrDance]

Thanks for the reply substitute,

You are right it is an integer.

I tried your suggestion but it's not returning any records for some reason.

I should add that only certain records contain a value for TABMin or TABMax

eg.
Number RES TAB
1 1 3
2 2
3 3
4 4
5 5
6 6
1 1 2
2 2
3 3
4 4
5 5
6 6
1 1 3
2 2
3 3
4 4
5 5
6 6
etc

So basically I need to show ALL records if no values are entered into TABMin or TABMax.

 

[DrDance]

SOLUTION FOR ANYONE SEARCHING LATER....

Substitute was right with his formula

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.

Thanks substitute for most of the solution.