- Joined
- Jul 18, 2007
- Messages
- 2
- Reaction score
- 0
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] ),
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.
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]![txtEndDate]),
>=[Forms]![frmShopping]![txtStartDate],
BETWEEN [ Forms]![frmShopping]![txtStartDate] AND [Forms]![frmShopping]![txtEndDate]) )
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.
Last edited: