Nz / Between... And... Help

[Rjc8513]

I have a query with a single table, [tbl CANCELLATION DATA] as it's source. In the query design grid, I am trying to refer to two combo boxes on the form [frm CANCELLATIONS].

The two combo boxes are requiring the user to enter a start date and an end date. I am trying to use the user input as the paramaters for the field [DATA COLLECTION DATE].

If the user enters no start date and no end date, I want all dates to be returned. If the user enters only a start date and no end date, I want all dates returned. If the user enters no start date and only an end date, I want all dates returned. If the user enters both a starting and ending date, I want the range of dates selected to be returned.

I have experimented with the Nz function and the IsNull function but cannot get it to work under all circumstances.

Here is what I have as the criteria in the [DATA COLLECTION DATE] field in the query grid:

IIf(IsNull([Forms]![frm CANCELLATIONS].[cbx START DATE]) Or IsNull([Forms]![frm CANCELLATIONS].[cbx END DATE]),[DATA COLLECTION DATE],Between [Forms]![frm CANCELLATIONS].[cbx START DATE] And [Forms]![frm CANCELLATIONS].[cbx END DATE])

 

[PHV]

You may try this:
Between [Forms]![frm CANCELLATIONS].[cbx START DATE] And [Forms]![frm CANCELLATIONS].[cbx END DATE] Or [Forms]![frm CANCELLATIONS].[cbx START DATE] Is Null Or [Forms]![frm CANCELLATIONS].[cbx END DATE] Is Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Rjc8513]

Thanks PHV! That did it.