Filter

[adams.bria]

Wanting to make a filter that looks in multiples fields for a set word. For example a "cat" filter, where if the word cat appears in any of 6 designated fields, it would filter to those results. I have been using

me.filter = "[field1] = 'cat'" 
filteron = true

but I am unsure of how to expand. Thanks

 

[boblarson]

Dim strFilter As String
 
strFilter = "[Field1]='cat' OR [Field2]='cat' OR [Field3]='cat' OR [Field4]='cat' OR [Field5]='cat' OR [Field6]='cat'"
Me.Filter = strFilter
Me.FilterOn = True

Now, if you want to use a text box for that, you would need to do something like:

Dim strFilter As String
 
strFilter = "[Field1]='" & Me.TextBox & "' OR [Field2]='" & Me.TextBox & "' OR [Field3]='" & Me.TextBox & "' OR [Field4]='" & Me.TextBox & "' OR [Field5]='" & Me.TextBox & "' OR [Field6]='" & Me.TextBox & "'"
Me.Filter = strFilter
Me.FilterOn = True

But I question how good your table design is if you have so many fields like that.

 

[adams.bria]

Thanks for the help Bob! Hate it and love it when the answer is so easy and just isn't coming to me on a Monday.

DB data is being provided outside our control. Just need to find when our company is being mentioned in a field. Over 10k entries in the data, with only ~20 entries of relevance. Tis a sad day when I have to turn off my DB structure tendencies and suck up someone else's crappy data collection policies.

 

[boblarson]

Tis a sad day when I have to turn off my DB structure tendencies and suck up someone else's crappy data collection policies.

I hear you. Been there.

 

[adams.bria]

Sorry wasn't clear the first time... wouldn't "=" mean the field has to only be cat? What can I put there so that the field on needs to contain the word cat?

I know I have an example using macros and "like" but I can't get to it right now.

Thanks

 

[boblarson]

If you want just the start then

[Fieldx] Like 'cat*' OR ...etc.

if you want cat anywhere in the field

[Fieldx] Like '*cat*' OR ...etc.