Using Between with iif isnull

[TonyB]

Hi,

When users enter a date form ("orderdatefrom") and a date to ("orderdateto") on a form, they click the "Run Query" button and the query returns all results of orders placed within that date range. To do this, I use the following statement under the "OrderDate" field in my query:

Between [forms]![frmProjReq]![orderdatefrom] And [forms]![frmProjReq]![orderdateto]

This works fine, but in order for it to work, users are forced to enter a date range (otherwise the query returns no results because nothing is entered). How do I change it so that if no dates are entered, then the query displays all the records in the table? I have tried:

Iif(isnull([forms]![frmProjReq]![orderdatefrom] And [forms]![frmProjReq]![orderdateto]),[tblReqOrders].[OrderDate],Between [forms]![frmProjReq]![orderdatefrom] And [forms]![frmProjReq]![orderdateto])



But this does the opposite - it shows all data, but only if nothing is entered in the "orderdatefrom" and "orderdateto" boxes on my form. Access also changes the statement for some reason.

I've managed to work around this by using VBA to tell the form to input dates from 01/01/1900 to 31/12/2999 into the "orderdatefrom" and "orderdateto" boxes if they are are currently null, and then use the simple "between" statement at the top of this post, in the query. However, it's a bit messy and I think it would be better if I didn't have to do this.

Thanks

[pbaldy]

How about

Nz([forms]![frmProjReq]![orderdatefrom], #01/01/1900#)

[TonyB]

Thanks, but it constantly returns no values at all when I tried that statement, regardless of whether anything is entered or not.

[pbaldy]

Did you use it with Between and the other textbox with the appropriate date value?

[TonyB]

Awesome thanks!

Indeed the following worked:

Between Nz([forms]![frmProjReq]![orderdatefrom],#01/01/1900#) And Nz([forms]![frmProjReq]![orderdateto],#01/01/2099#)

Cheers man!

[pbaldy]

Happy to help!