Filter using wildcard (1 Viewer)

notsirk · Oct 24, 2012

Hello! I am relatively new to vba but I am working on a database built by my predecessor and I need help with a form filter.

This form currently has a filter with the code:

Code:

Forms![frmMenu]!sbfSubForm.Form.Filter = "endDate Is Null"

I want to add another filter that looks at a serial number field and displays only serials that begin with 8 but I don't know to write this. I've tried variations of the following but keep getting errors:

Code:

Forms![frmMenu]!sbfSubForm.Form.Filter Like "serialNumber = 8*"

Obviously, I don't know what I'm doing so any help would be appreciated!

pbaldy · Oct 24, 2012

Try

Forms![frmMenu]!sbfSubForm.Form.Filter = "serialNumber Like '8*'"

notsirk · Oct 24, 2012

Well, it's better but the results are incorrect.

My serialNumber field has numbers (stored as text) that either begin with an "8" and are 8-digits long or begin with a "5" and are 7-digits long. When I filter for the former, the list includes some beginning with "5" and do not include all that begin with "8". Same but reversed for the second filter.

Also, there is a separate filter running simultaneously that filters out units that are inactive (this is the first line of code I included in my first post). Could that cause a conflict?

So here's what I'm expecting:
Filter 1 removes inactive units. Then filter 2 displays only the specified units from the results of the first filter.

Would it not work this way?

pbaldy · Oct 24, 2012

That code is changing the filter, not adding to it. To filter on multiple items you'd do something like:

"endDate Is Null And serialNumber Like '8*'"

notsirk · Oct 24, 2012

I still get the exact same results. Perhaps if you see my full code?

Code:

 Private Sub tglLocCurrent_Click()
   
  'when changing form properties from a different form the ".Form." part is needed!!!
      Forms![frmMenu]!sbfSubForm.Form.Filter = "endDate Is Null"
      Forms![frmMenu]!sbfSubForm.Form.FilterOn = True
      Forms![frmMenu]!sbfSubForm.Form.Requery
   
      Forms![frmMenu]!tglLocCurrent = True
      Forms![frmMenu]!tglLocAll = False
   
  End Sub
   
  Private Sub tglLocAll_Click()
   
  'when changing form properties from a different form the ".Form." part is needed!!!
      Forms![frmMenu]!sbfSubForm.Form.FilterOn = False
      Forms![frmMenu]!sbfSubForm.Form.Requery
   
      Forms![frmMenu]!tglLocCurrent = False
      Forms![frmMenu]!tglLocAll = True
   
  End Sub
   
  Private Sub tglUnit8000_Click()
   
  'when changing form properties from a different form the ".Form." part is needed!!!
      Forms![frmMenu]!sbfSubForm.Form.Filter = "endDate Is Null And serialNumber Like '8*'"
      Forms![frmMenu]!sbfSubForm.Form.FilterOn = True
      Forms![frmMenu]!sbfSubForm.Form.Requery
   
      Forms![frmMenu]!tglUnit8000 = True
      Forms![frmMenu]!tglUnitAll = False
      Forms![frmMenu]!tglUnit5000 = False
   
   
  End Sub
   
  Private Sub tglUnit5000_Click()
   
  'when changing form properties from a different form the ".Form." part is needed!!!
      Forms![frmMenu]!sbfSubForm.Form.Filter = "endDate Is Null And serialNumber Like '5*'"
      Forms![frmMenu]!sbfSubForm.Form.FilterOn = True
      Forms![frmMenu]!sbfSubForm.Form.Requery
   
      Forms![frmMenu]!tglUnit5000 = True
      Forms![frmMenu]!tglUnitAll = False
      Forms![frmMenu]!tglUnit8000 = False
   
   
  End Sub
   
  Private Sub tglUnitAll_Click()
   
  'when changing form properties from a different form the ".Form." part is needed!!!
      Forms![frmMenu]!sbfSubForm.Form.FilterOn = False
      Forms![frmMenu]!sbfSubForm.Form.Requery
   
      Forms![frmMenu]!tglUnit8000 = False
      Forms![frmMenu]!tglUnitAll = True
      Forms![frmMenu]!tglUnit5000 = False
   
  End Sub

I'm still confused as to why the tglUnit8000 includes 5000 units. It also still excludes the majority of 8000 units.

pbaldy · Oct 24, 2012

Can you post a sample db here that does this?

notsirk · Oct 24, 2012

I found another way to get what I'm looking for! There are two other fields in the form where one will be null and the other won't depending on the unit type so I just did:

Code:

Forms![frmMenu]!sbfSubForm.Form.Filter = "locNP Is Not Null And locHost Is Null"

And vice versa for the other toggle.

Thanks for your help!

pbaldy · Oct 24, 2012

Curious why the other didn't work, but glad you got it sorted out!

Filter using wildcard (1 Viewer)

notsirk

Registered User.

pbaldy

Wino Moderator

notsirk

Registered User.

pbaldy

Wino Moderator

notsirk

Registered User.

pbaldy

Wino Moderator

notsirk

Registered User.

pbaldy

Wino Moderator

Similar threads

Users who are viewing this thread