Apply a second filter keeping the first one (1 Viewer)

dim · Jul 18, 2018

Hi,

I have a form where I need to apply two filters one at the time, using two cbo box: cbo_State for the field State and cbo_Status for the field Status.
I tried this, but doesn't work:

For State:

Private Sub cbo_State_AfterUpdate()
If Me.FilterOn = True Then
Me.Filter = "State= " & Me.cbo_State & " AND Status= " & Me.cbo_Status
Else
Me.Filter = "[State]='" & Me![cbo_State] & "'"
Me.FilterOn = True
End If
End Sub

and similar for the Status:

Private Sub cbo_Status_AfterUpdate()
If Me.FilterOn = True Then
Me.Filter = "State= " & Me.cbo_State & " AND Status= " & Me.cbo_Status
Else
Me.Filter = "[Status]='" & Me![cbo_Status] & "'"
Me.FilterOn = True
End If
End Sub

Can you help me please?
Thanks!

Ranman256 · Jul 18, 2018

if field is a string you must use a single quote:
(i added extra space by the single quote so you can see it)

"State= ' " & Me.cbo_State & " ' AND Status= ' " & Me.cbo_Status & " ' "

pbaldy · Jul 18, 2018

I'm not sure I like your test of FilterOn; it could be that the user had previously filtered by the same field, not the other one. I would have a form-level function that tested each control and conditionally built a filter, and call that from both places.

dim · Jul 18, 2018

Thank you Ranman256, but I get an error when I use your code:
"State= ' " & Me.cbo_State & " ' AND Status= ' " & Me.cbo_Status & " ' "

The verification for the first filter it was applied is ok using:
If Me.FilterOn = True Then
...

pbaldy · Jul 18, 2018

dim said:
The verification for the first filter it was applied is ok using:
If Me.FilterOn = True Then
...

If that was in answer to my post, try this test: filter by a state, then without touching status filter by a different state.

dim · Jul 18, 2018

the second filter show empty results...

pbaldy · Jul 18, 2018

Sounds like it proved my point.

dim · Jul 18, 2018

Thank you Pbaldy. I know this can be done using a query with the conditions Like "*" & [Forms]![Customers]![cbo_State] & "*" for State and Like "*" & [Forms]![Customers]![cbo_Status] & "*"
My question is if there is a way to be done using me.filter...

pbaldy · Jul 18, 2018

Yes, I outlined it in my first post.

MajP · Jul 18, 2018

This is untested, but the easiest approach is to build a single function for no matter how many controls

Code:

Public Function GetFilter() as string
  dim strState as string
  dim strStatus as string
  dim strFilter as string
  'add more for each control
  if not Isnull(me.cbo_state) then
    StrState = “[State] =  ‘” & me.cbo_state & “’ AND ”
     ‘ remove single quotes if numeric
  end if
  if not isnull(me.cbo_status)
     strStatus = "Status = "" & Me.cbo_Status & "'"
     'remove single quotes if numeric
  end if
  strFilter = strState & " AND " & strStatus 
  strFilter = trim(strFilter)
  if Right(strFilter, 4) = " AND" then
    StrFilter = Left(strFilter, len(strFilter) - 4)
  end if
  'check the code to make sure it works.
  debug.print strFilter
  GetFilter = strFilter
end function

Then you call from each control

Code:

Private Sub cbo_State_AfterUpdate()
   Dim strFilter as string
   strFilter = GetFilter
   me.filter = strFilter
   me.filteron = ( strFilter <> “”) 
end Sub

pbaldy · Jul 18, 2018

There's your fish, though I'd do it all in the function.

dim · Jul 18, 2018

Thank You MajP,

For now I use the other solution using a query, but I'll test your function.
This could be very usefully to filter using multiple fields (more then two)

Apply a second filter keeping the first one (1 Viewer)

dim

Registered User.

Ranman256

Well-known member

pbaldy

Wino Moderator

dim

Registered User.

pbaldy

Wino Moderator

dim

Registered User.

pbaldy

Wino Moderator

dim

Registered User.

pbaldy

Wino Moderator

MajP

You've got your good things, and you've got mine.

pbaldy

Wino Moderator

dim

Registered User.

Similar threads

Users who are viewing this thread