Results 1 to 2 of 2

ComboBoxes to Filter Query, ignore filters when null

  1. #1
    hehrerh is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    4

    ComboBoxes to Filter Query, ignore filters when null

    Hello! See the attached .zip for a word doc that includes all of the important images that go with this post. Image locations in the body text of this post will be marked as [image]

    My question relates to filtering a query based on multiple ComboBoxes, and ignoring the filters when the ComboBoxes are left blank.



    Please open the attached .zip and view my question there. The images are integral to my question.

    [image]

    The Goal:
    I have a dashboard from which I would like to launch reports. The idea is to have 8 ComboBox filters (1) that apply to various reports (2). Eventually, I will have 10-15 reports based on queries and filtered based on these ComboBox selections.
    The way the filters should work:
    The ComboBox filters should engage when the ComboBox contains a value, but ignored when it is left blank.
    What did NOT work:
    I have tried various criteria to filter the queries upon which these reports are based. The one that seems like it definitely SHOULD work and would solve all of my problems can be seen here:

    [image]

    In this case, Combo109 is the ComboBox on my dashboard that filters based on ‘Region’. I know that the first part of this statement reads correctly, because when ComboBox109 has a selection, the records filter accordingly:

    [image]

    [image]

    However, when the ComboBox is left empty – no data is returned:

    [image]

    I know this must be some issue with IIF statements in criteria. I know this because if I break out each possible result of the IIF statement and use them independently, the query runs fine:

    [image]

    …maddening. In any case, this form will not work, because this criteria returns no values when the ComboBox is left blank.
    What worked a LITTLE bit:
    I found a decent solution that did produce the desired results:

    [image]

    This filter applies the ComboBox selection as a filter UNLESS the ComboBox is left blank. Perfect! The only drawback: it automatically creates a second column as shown:

    [image]

    This works quite well for one or two criteria. However, exponentially more Criteria lines are generated as more and more filters are used. And I have 8 of them and could add some more. The result is an exponential increase in criteria lines:

    [image]

    …Not a sustainable solution.
    As might be expected, this eventually leads to too many Critera rows and the Query grows too large:
    [image]

    Other problem with this method:
    1.Difficult to revise.
    a. As new filters are added, all filter fields must be deleted and re-added to type the original filter formula as shown:
    b. =[Forms]![_Home]![Combo109] OR [Forms]![_Home]![Combo109] Is Null
    So what should I do?
    The internet has not been helpful. Most articles I have found focus on the FIRST solution, with the IIF statement, but nearly all peter out without an actual usable solution. The solution that I have been using works for up to 6 ComboBoxes, but is a terror to revise.
    Perhaps I’m going about this an entirely wrong way? How would you filter your reports without making them too complex? I would love to hear your input and get myself to a better solution.
    Thanks!
    Attached Files Attached Files

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    5,143
    use a continuous form to show all records, or for a report,
    when the user fills in the boxes, then clicks the RUN button, use vb to build the where clause:
    (NULLS ARE NOT USED)

    Code:
    sub btnFind_click()
    dim sWhere as string
    
    if not IsNull(txtBox1) then sWhere = sWhere & " and [field1]='" & me.txtBox1 & "'"
    if not IsNull(txtBox2) then sWhere = sWhere & " and [field2]='" & me.txtBox2 & "'"
    if not IsNull(txtBox3) then sWhere = sWhere & " and [field3]='" & me.txtBox3 & "'"
    
    if sWhere = "" then
       me.filterOn = false
    else
        sWhere = mid(sWhere,5)       'remove the 1st 'and'
       me.filter = sWhere
       me.filterOn = true
    endif
    end sub
    docmd.openreport "rMyRpt",acViewPreview,,sWhere

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 02-20-2017, 10:28 PM
  2. Replies: 3
    Last Post: 03-31-2016, 11:16 AM
  3. Need Ranking to Ignore Null
    By evil44 in forum Queries
    Replies: 2
    Last Post: 01-25-2016, 05:04 AM
  4. Replies: 3
    Last Post: 05-10-2013, 09:49 PM
  5. Search to ignore null fields
    By tommy93 in forum Queries
    Replies: 10
    Last Post: 02-07-2012, 09:58 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums