Date Range Criteria Allowing Blank Entries

[wesmorgan7]

I am trying to build an Access query in design view which allows user entry of starting date, ending date, (or Both or Neither). I have a form with unbound text boxes txtStartDate and txtEndDate, but am having much difficulty getting the query to give proper results.

Goal:
* If both boxes are left blank, then return ALL records in the "Tools" table (which the query is built on) would be shown.
* If txtStartDate is left blank but txtEndDate entered, then return records before (and including) txtEndDate.
* If txtStartDate is entered but txtEndDate left blank, then return records after (and including) txtStartDate.
* If both boxes have data entered, then return records in the range (inclusive).

Here is what I have been trying (a nested IIF combined with IsNull), but it isn't working. Is there anything glaringly obvious about why this won't work? I haven't been using Access all that long, so I could easily have some syntax issues. (I added whitespace and carriage returns for readability only here)



IIf( ISNULL( [Forms]![frmShopping]![txtStartDate] ),

IIf( ISNULL([Forms]![frmShopping]![txtEndDate]),

[Tools].[Date],

<=[Forms]![frmShopping]![txtEndDate]),

​

IIf([Forms]![frmShopping]![txtEndDate] Is Null,

>=[Forms]![frmShopping]![txtStartDate],

BETWEEN [ Forms]![frmShopping]![txtStartDate] AND [Forms]![frmShopping]![txtEndDate]) )

​

Note - I am open to suggestions for different methods if there is a better way to go about this. This isn't the first time the dates question has come up, but I couldn't find anything in the posts that included the open-ended option.