Solved

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

Posted on 2014-01-24
3
687 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

895 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

16 Experts available now in Live!

Get 1:1 Help Now