Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2014-01-24
3
Medium Priority
?
699 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 48

Expert Comment

by:Dale Fye
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 48

Accepted Solution

by:
Dale Fye earned 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

636 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