Solved

access 2010 filter a query with/without dates with/without combobo value

Posted on 2014-01-24
3
686 Views
Last Modified: 2014-01-24
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].[NavMenuSubform].[Form]![Reports_Reports]![cboFacilityRefID]
OR [Forms]![frmNavMenuForm].[NavMenuSubform].[Form]![Reports_Reports]![cboFacilityRefID] & "" = ""

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]![frmNavMenuForm].[NavMenuSubform].[Form]![Reports_Reports]![txtStartDate] Is Null,"",[Forms]![frmNavMenuForm].[NavMenuSubform].[Form]![Reports_Reports]![txtStartDate]) And IIf([Forms]![frmNavMenuForm].[NavMenuSubform].[Form]![Reports_Reports]![txtEndDate] Is Null,"",[Forms]![frmNavMenuForm].[NavMenuSubform].[Form]![Reports_Reports]![txtEndDate])

many thanks
0
Comment
Question by:andrewpiconnect
  • 2
3 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
Andrew,

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:
Private Sub cmd_Filter

    Dim varFilter as Variant

    varFilter = NULL

    if me.cboFacilityRefID & "" <> "" then
        varFilter = "[FacilityRefID] = " & me.cboFacilityRefID
    endif

    if me.txtStartDate & "" <> "" Then
         varFilter = (varFilter + " AND ") & "[StartDate] >= #" & me.txtStartDate & "#"
    endif

    if me.txtEndDate & "" <> "" Then
         varFilter = (varFilter + " AND ") & "[EndDate] < #" & (cdate(me.txtEndDate) + 1) & "#"
    endif

    if varFilter & "" <> "" Then
        me.Filter = varFilter
        me.FilterOn = true
    Else
        me.Filter = ""
        me.FilterOn = False
    End if

End Sub

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.
0
 

Author Comment

by:andrewpiconnect
Comment Utility
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
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
Comment Utility
One way would to create a second query "yourquerynamefiltered" with the same SQL as the original query (the one used for the form).

Then in the Filter button, you could apply the criteria to that 2nd query, something like:
if varFilter & "" <> "" then
    me.Filter = varFilter
    me.FilterOn = True
    currentdb.querydefs("yourquerynamefiltered").SQL = currentdb.querydefs("yourqueryname").SQL & " WHERE " & varFilter
Else
    me.Filter = ""
    me.FilterOn = False
    currentdb.querydefs("yourquerynamefiltered").SQL = currentdb.querydefs("yourqueryname").SQL
end if

Open in new window

Then use "yourquerynamefiltered" as the value for strQqyName in your OutputTo statement

Or you could apply the WHERE clause to the original query  with syntax similar to the above.  I find it is generally quicker to filter the form than to requery the recordset.  The down side of that, is that the requery actually updates the data to include any new records, exclude any deleted records, and update any changed records since you originally populated the form.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now