query criteria for date range 1

[Kyosa]

background:
i have a form driving a report query with 2 unbound text boxes to enter a From and To date range. on my form i also have abt 4 other unbound fields all driving the report. Not all the fields are required to be entered.


i am using the following as the criteria for my date in the Query:

Between [Forms]![Mail_Log_Rpt]![Date_From] And [Forms]![Mail_Log_Rpt]![Date_To]

all nice and fine...however i need a way such that If they leave the Date_From and Date_To blank on the form, it pulls back all dates.

i have tried several different things and can get either one or the other to work but not when i try to come up with a combined formula... any ideas????



Kyosa


"The key to flexibility is indecision!"

 

[scriverb]

Try this:

Between NZ([Forms]![Mail_Log_Rpt]![Date_From],[[i][red]currentdatefield[/red][/i]])And NZ([Forms]![Mail_Log_Rpt]![Date_To], [[i][red]currentdatefield[/red][/i]])

The NZ function checks both of the form controls for Null and if found returns the DateField in your query that is being checked. (i.e. the field name just before the in this query "Between") What will happen is that it will be compared against itself. So, all will be selected.

post back if you have any questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[kyosakc]

worked like a charm!! thanks very much for your help.!