?
Solved

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

Posted on 2014-01-24
3
Medium Priority
?
703 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 49

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 49

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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
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 …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

600 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