Solved

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

Posted on 2014-01-24
3
690 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)
ID: 39806369
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
ID: 39806386
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
ID: 39806431
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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 …

839 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