andrewpiconnect
asked on
access 2010 filter a query with/without dates with/without combobo value
Hi,
I have a saved query that i run from a command button on a form that exports the results to an excel spreadsheet. On the form I have a combobox (cboFacilityRefID) to filter the results of the query [FacilityRef_ID]. This works fine when a value is either selected or not selected using the below:
WHERE [yourField] = [Forms]![frmNavMenuForm].[ NavMenuSub form].[For m]![Report s_Reports] ![cboFacil ityRefID]
OR [Forms]![frmNavMenuForm].[ NavMenuSub form].[For m]![Report s_Reports] ![cboFacil ityRefID] & "" = ""
However, i want to add an additional filter to the [InceptDate] of between two dates in addition to the combo filter above but again, the date fields will either both have a value or both may be empty (there will never be a situation when only one date field has a value.
I have tried to add this to the query along with the above but i cannot get it to work:
Between IIf([Forms]![frmNavMenuFor m].[NavMen uSubform]. [Form]![Re ports_Repo rts]![txtS tartDate] Is Null,"",[Forms]![frmNavMen uForm].[Na vMenuSubfo rm].[Form] ![Reports_ Reports]![ txtStartDa te]) And IIf([Forms]![frmNavMenuFor m].[NavMen uSubform]. [Form]![Re ports_Repo rts]![txtE ndDate] Is Null,"",[Forms]![frmNavMen uForm].[Na vMenuSubfo rm].[Form] ![Reports_ Reports]![ txtEndDate ])
many thanks
I have a saved query that i run from a command button on a form that exports the results to an excel spreadsheet. On the form I have a combobox (cboFacilityRefID) to filter the results of the query [FacilityRef_ID]. This works fine when a value is either selected or not selected using the below:
WHERE [yourField] = [Forms]![frmNavMenuForm].[
OR [Forms]![frmNavMenuForm].[
However, i want to add an additional filter to the [InceptDate] of between two dates in addition to the combo filter above but again, the date fields will either both have a value or both may be empty (there will never be a situation when only one date field has a value.
I have tried to add this to the query along with the above but i cannot get it to work:
Between IIf([Forms]![frmNavMenuFor
many thanks
ASKER
Having the filter with the minimum number of criteria is an issue i have just realised tbh.
I currently use a vba module to examine which fields are populated and then run 1 of 4 saved queries based on that.
Building a dynamic where clause makes a lot of sense but isnt that only useful to filter the forms recordset? The query i am wanting to run is a separate query that exports the results to excel and saves the outputted file to a folder.
How can i implement your dynamic where clause into this?
DoCmd.OutputTo acOutputQuery, strQryName, "Excel97-Excel2003Workbook (*.xls)", strReportPath & strReportFileName, True, "", 0, acExportQualityPrint
I currently use a vba module to examine which fields are populated and then run 1 of 4 saved queries based on that.
Building a dynamic where clause makes a lot of sense but isnt that only useful to filter the forms recordset? The query i am wanting to run is a separate query that exports the results to excel and saves the outputted file to a folder.
How can i implement your dynamic where clause into this?
DoCmd.OutputTo acOutputQuery, strQryName, "Excel97-Excel2003Workbook
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
When I want to filter by more than one field, I generally don't put that criteria in the Query itself, I generally build it on the fly and apply the filter to the form, rather than the query.
I don't do this using the AfterUpdate event of the filter controls because that would cause the recordset to refilter immediately after changing any of those values. Instead, I use a command button, call it cmdFilter, and usually add a second button Clear Filter. In the cmdFilter Click event, I evaluate all of the possible conditions and build the Filter string on the fly. Something like:
Open in new window
Then, I use the click event of cmd_Clear_Filter to simply set the Filter property to "" and the FilterOn property to False.You can, of course, build those criteria into the query itself, and simply do a requery when you click the Filter button, but I find that when you start doing that, the queries become overly complex and occasionally generate errors of the order ("the query is too complex"). By building the filter on the fly, you avoid that and ensure that the filter has the minimum number of criteria.