Multiple comboboxes as criteria (1 Viewer)

[Jupie23]

I have a 2 part question I am hoping to get some help with. I should also add that I'm not very well versed in queries and I mostly stick to select queries, so I may need specific direction if another type is needed. I am building a database that imports a report of accounts that are missing documentation. They open the form and work each account, and then either pend it or mark it complete and it leaves that queue. The form is based on a query that pulls only accounts that have not been marked pended or completed.

First question:

They would like the ability to filter the accounts that come up in the form by 3 different comboboxes, in case they want to work certain scenarios.

Comboboxes are for:

Document missing (cboException)
Days aged (cboAged)
Remaining balance (cboBalance)

Document missing is easy, I just added criteria in my original query to refer to Forms!frmMain!cboException. Days aged and balance are tripping me up because they want a range in the combobox. I put the ranges into tables for both with a start and end number (Days aged: 150 - 330, 331 - 510, 511 - 690, etc. Balance: $0 - $5,000, $5,001 - 10,000, 10,001 - 15,000, etc.) and then I concatenated the fields in the combo to include the dash between.

How do I set up the query to pull accounts within the range selected in the combobox? I tried to refer to the 2 columns in the criteria with start and end number, like Between Forms!frmMain!cboAged.Column(0) and Forms!frmMain!cboAged.Column(1), but it doesn't seem to work. The last number in the range is also just a greater than, like for balance: $100,000 +, so there is a start number but not an end.


My second question is, can the query be set up in a way that they can select any of the 3 comboboxes in any combination and it will filter by whatever is selected? So they could say "I want to see accounts where contract is missing, it is older than 300 days, and the balance is over $10,000." Or they could just pick one and the others don't filter but show all.

Any help with this would be greatly appreciated!

 

[MajP]

For each control you do something like this to return all records if left blank
[forms]![Frm1].[Txt1] Or [forms]![Frm1].[Txt1] Is Null

Column will not work in a query. You will have to bind the first column and then save the value of the second column to another (hidden if you want) textbox. Update this on the after update event of the combo box.

 

[MajP]

I put the ranges into tables for both with a start and end number (Days aged: 150 - 330, 331 - 510, 511 - 690, etc. Balance: $0 - $5,000, $5,001 - 10,000, 10,001 - 15,000, etc.)

If these are in a table then put a PK on each range. Then filter by pk like
Where DaysAgedID = 3

 

[Jupie23]

Thank you for your response. I put a PK on each range and bound the combo to the Pk. Then I'm a little lost. Do I make a text box for Aged Start and Aged End, and after update on the combo, have it fill the values in each textbox? Then in my query do "between AgedStart and AgedEnd"?

 

[MajP]

Then I'm a little lost. Do I make a text box for Aged Start and Aged End, and after update on the combo, have it fill the values in each textbox? Then in my query do "between AgedStart and AgedEnd"?

Sorry, I think I kind of confused myself on that one. No you would have to then do what I said before and put those values into textboxes. But that is going to be really complicated to write in the query with all of those control references. The better solution for this will be to do it via code to build a filter or the sql.

 

[MajP]

This is my way to do this. This is not checked so you will have to debug by printing the string and looking at what it builds. Obviously you will have to modify all the names to match your fields and controls

Public Function getFilter() As String
  On Error GoTo errLabel
  '---------------------------------------------------------------------------------------------------
  'Need a variable for each control that creates part of the filter This example has 12 combos
  Dim strDocMissing As String
  Dim strAged As String
  Dim strBalance As String
  Dim andOR As String
  Dim removeEnd As Integer
  
  'I always have a way to make the filter an AND filter or an OR Filter using a frame with option box if not hard wire.
     If Me.framAndOr.Value = 1 Then
      andOR = " OR "
      removeEnd = 4
    Else
      andOR = " AND "
      removeEnd = 5
    End If
  '--------------------------------------------------------------------------------------    
    'Need an If for each control 
    If Not Trim(Me.cboDocMissing & " ") = "" Then
        strDocMissing = "[DocMissing] = '" & Me.cboDocMissing & "'" & andOR
    End If
    
    If Not Trim(Me.cboAged & " ") = "" Then
        strAged = "[DaysAged] between " & Me.cboAged.column(1) & " AND " & me.cboAged.column(2) & andOR
       ' Debug.Print "series " & strSeries
    End If
    
    If Not Trim(Me.cboBalance & " ") = "" Then
        strBalance = "[Balance] Between " & Me.cboBalance.column(1) & " AND " & me.cboBalance.column(2) & andOR
    End If
    
    '-----------------------------------------------------------------------------------------------------------------------  
    'Combine the pieces
    getFilter = (strDocMissing + strAged + strBalance)
 '--------------------------------------------------------------------------------------------------------------------------------------  
   'Remove the AND or the OR off the end
   If Not Trim(getFilter & " ") = "" Then
      getFilter = Left(getFilter, Len(getFilter) - removeEnd)
    End If
   'You may comment this out
    Debug.Print "Filter Criteria: " & getFilter
  Exit Function
errLabel:
  MsgBox Err.Number & "  " & Err.Description
End Function

 

[Jupie23]

Thank you MajP, I will give that a try!

 

[Jupie23]

Where would I put this code? On the On load event of the form? And do my comboboxes need to be on the form header? They are currently on a main menu type form by the button that opens the form.

 

[MajP]

In each combos after update you can call FilterForm

Then add this as well

Public Sub FilterForm
  dim strFilter as string 
  strFilter = getFilter()
  me.filter = strFilter
  me.filteron = false
  if strFilter <> "" then me.filteron = true
end sub

Before trying the filter see if you can simply debug.print the returned string and ensure it works for all cases.

 

[MajP]

If you have a demo you can post. Just would need the form and the table/query. I would put the combos in a header or footer. This ensures they show regardless of the form sizing.

 

[Jupie23]

Ok, here is a demo. I deleted a lot of stuff, so hopefully this is enough info. frmMain has the 3 combos. The fields I want to filter by are all in the top grey box on frmTobeProcessed.

cboException = Exception Type
cboBalance = Principal Balance
cboAged = Days Aged. Days aged is a calculated field that counts # of days between the create date and today.

Thank you so much for taking a look!

 

[MajP]

Since you are not filtering the current form you just need to pass it in.
I changed the aged cbo to be the same format. range shown and upper and lower are the hidden columns. I believe the field is called Exception Type and it is numeric. I replaced Exception Reason with type to make this work. In the query you need to get rid of all the control parameter references and this will work.
I cannot post the DB, my computerblocks the application.

Private Sub CommandProcess_Click()
  DoCmd.OpenForm "FrmToBeProcessed", , , getFilter
  'For Demo
  MsgBox getFilter
End Sub

Public Function getFilter() As String
  On Error GoTo errLabel
  '---------------------------------------------------------------------------------------------------
  'Need a variable for each control that creates part of the filter This example has 12 combos
  Dim strException As String
  Dim strAged As String
  Dim strBalance As String
  Dim andOR As String
  Dim removeEnd As Integer
  
  'I always have a way to make the filter an AND filter or an OR Filter using a frame with option box if not hard wire.
    ' If Me.framAndOr.Value = 1 Then
      andOR = " OR "
      removeEnd = 4
    'Else
    '  andOR = " AND "
    '  removeEnd = 5
  '  End If
  '--------------------------------------------------------------------------------------
    'Need an If for each control
    If Not Trim(Me.cboException & " ") = "" Then
        strException = "[Exception Type] = " & Me.cboException & andOR
    End If
    
    If Not Trim(Me.cboAged & " ") = "" Then
        strAged = "[Days Aged] between " & Me.cboAged.Column(1) & " AND " & Me.cboAged.Column(2) & andOR
       ' Debug.Print "series " & strSeries
    End If
    
    If Not Trim(Me.cboBalance & " ") = "" Then
        strBalance = "[Principal] Between " & Me.cboBalance.Column(1) & " AND " & Me.cboBalance.Column(2) & andOR
    End If
    
    '-----------------------------------------------------------------------------------------------------------------------
    'Combine the pieces
    getFilter = (strException + strAged + strBalance)
 '--------------------------------------------------------------------------------------------------------------------------------------
   'Remove the AND or the OR off the end
   If Not Trim(getFilter & " ") = "" Then
      getFilter = Left(getFilter, Len(getFilter) - removeEnd)
    End If
   'You may comment this out
    Debug.Print "Filter Criteria: " & getFilter
  Exit Function
errLabel:
  MsgBox Err.Number & "  " & Err.Description
End Function

 

[MajP]

I hard wired this to be an OR filter, you can add a frame to make selectable or comment out AND if you want that instead

' If Me.framAndOr.Value = 1 Then
      andOR = " OR "
      removeEnd = 4
    'Else
    '  andOR = " AND "
    '  removeEnd = 5
  '  End If

 

[Jupie23]

Thank you so much, it works!!! I'm so excited! My only question is, that message box that pops up when the form opens - I see that it opens up a blank message when nothing is selected. Is there a way to customize it at all? I would also rather have it say the exception name instead of the number type, but that's not a big deal. I could just remove the popup altogether, but it's kind of nice to see the filter.

 

[MajP]

I would also rather have it say the exception name instead of the number type
If the number and name are related in other words you have a related name for each number then you can do the same trick and show the name and hide the number. If this is different field you are talking about then you would modify this

    If Not Trim(Me.cboException & " ") = "" Then
        strException = "[Exception Type] = " & Me.cboException & andOR
    End If

to be

    If Not Trim(Me.cboException & " ") = "" Then
        strException = "[Exception Field Name] = '" & Me.cboException "'" & andOR
    End If

Notice I added single quotes if text. That is because in sql
[Exception Type] = 1
but
[Exception Name] = 'SomeText'

The pop up was for your use. You can comment it out.
Or do something like

Private Sub Command0_Click()
   dim strFilter as string
   strFilter = getFilter
   if strFilter = "" then
     Msgbox "No filters applied" 
   else
     msgbox "Your filter is: " & vbcrlf & strFilter
   end if
  DoCmd.OpenForm "FrmToBeProcessed", , , getFilter
End Sub

 

[Jupie23]

Changing to name instead of type didn't work for me, but I'm not going to worry about it. I added to the message box and I am happy with how it came out. Thank you so much for taking the time to help with this, I really appreciate it!!

 

[MajP]

Changing to name instead of type didn't work for me

In the example provided there is no Exception Reason or Exception Name field so that may be the reason. Check the field exists in your query.