Optional Date Filter 1

[Nilo]

What is the best way to create an OPTIONAL date filter on a form?
I'm completely stumped.
I have a query/form combination in which i would like the user to be able to select a date range only if they want to. So my form has a textbox named FromDate and another textbox named ToDate. These values will be used in the criteria line in the query that feeds the form. The field that I want to filter on in the query is ActivityDate.
Here's where everything starts breaking down. I can use a Between statement using those textbox values and it works fine.
The query works fine with this statement in the criteria line.

Between [Fromdate] And [Todate]

However, when I try to add an iif statement into the criteria to make the criteria optional, I get no results.
This is what my latest attempt looks like...

IIf([FromDate] Is Not Null And [ToDate] Is Not Null,Between [Fromdate] And [Todate],[Activitydate])

now i've tried several combinations of nulls and " " and whatever else i can think of and nothing works but I am willing to try any suggestions anybody has.
Are the textboxes even the right way to go for something like this? I don't want to paint myself into a corner by thinking I NEED to use textboxes. Any help would be greatly appreciated...

 

[dhookom]

If the controls are on a form, I would expect to see a where clause containing something like:
[TT][RED]
Between Forms!frmDates!FromDate and Forms!frmDates!ToDate
[/red][/tt]
Also, does this query feed a report or form? If so, you should consider removing the criteria completely from the query and use the WHERE clause in DoCmd.OpenForm or DoCmd.OpenReport.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[SeeWard]

This too may be of use:

http://www.microsoft-accesssolutions.co.uk/blank_query_parameter.htm

 

[Nilo]

I have an unbound form called fActivities, within this form, I have a subform called fActDetail which has a query called qActivities as it's Record Source. The subform is set as a Continuous Form. I have a field called ActDate (Date/Time datatype) from a table called tActivityResults, which is the source for the qActivities query.
I have 2 textboxes on the fActivites form called FromDate and ToDate which i would like to use to create an OPTIONAL date range around the ActDate field.
The following criteria, when placed in the criteria section of the ActDate field within the qActivities query,

Between [Fromdate] And [Todate]
or
Between Forms!frmDates!FromDate and Forms!frmDates!ToDate

both work
(that's not the issue.)
but at least this establishes that there is not a conflict between the values that are being entered in the FromDate and the ToDate fields and the format or datatype or values that are stored in the ActDate field that I am using in the qActivities query
It's when I try to include either of the above Between statements in an IIF statement that I get no results.

Here is an example of a functioning iif statement that uses a combobox for the optional criteria value....
(this is copied straight from the qActivities query)

IIf([Forms]![fActivties]![cmbStatus] Is Null,[tActivityResults]![Status],[Forms]![fActivties]![cmbStatus])

this works like a charm

I thought the following would work if i placed it in the criteria of the ActDate field in the qActivities query but it doesn't

IIf([Forms]![fActivties]![FromDate] Is Not Null And [Forms]![fActivties]![ToDate] Is Not Null,[tActivityResults].[ActDate] Between [Forms]![fActivties]![FromDate] And [Forms]![fActivties]![ToDate],[tActivityResults]![ActDate])

Has anyone successfully done something like this?

 

[dhookom]

Try something like:
Between Nz(Forms!frmDates!FromDate, [ActDate]) and Nz(Forms!frmDates!ToDate, [ActDate])


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Nilo]

I always forget to give the Nz function a try. If i could give you 5 stars I would....plugged that right into the query and it worked! thanks!