How to apply filter in VBA - help appreciated

[mariekeb]

Hi there,



My name is Marieke and I'm very new to Access. I am trying to build a simple database for my work with a search function to search records. At my work they have Access 2007 so that's the version I am using.

I created a text box and a button, and when clicking the button I want the results to be filtered based on what's been typed. To do this, I created an on-click macro ApplyFilter which looks like this:

Where condition:

[Field 1] Like "*" & [Forms]![Query2]![Tekst24] & "*"

This works. However, I need more fields to be searchable (around 6) which I tried to add using Or, like this:

[Field 1] Or [Field 2] Or [Field 3] Or [Field 4] Or [Field 5] Or [Field 6] Like "*" & [Forms]![Query2]![Tekst24] & "*"

This doesn't work; only the first two fields are searched. Then I tried to make a seperate line like the first one (with the like condition seperate for each one), but then for all six. However, the argument is much too long then and the "Help" option suggested I'd do this in VBA because there you can use much longer arguments. I've tried the following:

DoCmd.ApplyFilter , [Field1] Like "*" & [Forms]![Query2]![Tekst24] & "*"

However, this does not work, not even for one field. Does anyone have any suggestions on how to solve this and make my button filter based on all six fields?

Any help is much appreciated, thanks in advance for your time!

[mariekeb]

Maybe useful for clarification: I used the method from this video, but since there are only 255 conditions allowed in a macro it does not fit. How do I do the same in VBA? Tnx!

[CJ_London]

two ways you can do this

[Field 1] Or [Field 2] Or [Field 3] Or [Field 4] Or [Field 5] Or [Field 6] Like "*" & [Forms]![Query2]![Tekst24] & "*"

by combining into one long string - use a pipe (|) as a separator since this is a character unlikely to be typed by the user

[Field 1] & "|" & [Field 2] & "|" & [Field 3] & "|" & [Field 4] & "|" & [Field 5] & "|" & [Field 6] Like "*" & [Forms]![Query2]![Tekst24] & "*"

or using OR properly

[Field 1] Like "*" & [Forms]![Query2]![Tekst24] & "*" OR [Field 2] Like "*" & [Forms]![Query2]![Tekst24] & "*" OR [Field 3] Like "*" & [Forms]![Query2]![Tekst24] & "*" Or [Field 4] Like "*" & [Forms]![Query2]![Tekst24] & "*" Or [Field 5] Like "*" & [Forms]![Query2]![Tekst24] & "*"Or [Field 6] Like "*" & [Forms]![Query2]![Tekst24] & "*"

[mariekeb]

Hi,

Thank you so much for your quick reply - it's very useful!

The second solution you mentioned is what I also tried before, but then I got an error saying there were too many characters in the condition.

However, your first solution seems to work! Would you mind explaining to me what adding the & "|" & does? I'd like to understand how this works

Thanks again

[CJ_London]

Would you mind explaining to me what adding the & "|" & does? I'd like to understand how this works

if your fields were

abc
def
ghi

without the | you would have

abcdefghi like....

so a user searching for cde would get a match

by using the |

you have

abc|def|ghi like....

so a user searching for cde would not get a match

[ssanfu]

..... I'm very new to Access......

Since you say you have one text box and you want to search (filter on) 6 fields, that tells me you probably have repeating fields like Contact1, Contact2, Contact3, ...

This is a spreadsheet design - typically short and wide.
A relational database table would be designed tall and narrow.

If I am right, you might want to analyze your table design structure before going much further. Tables designed like a spreadsheet will cause you a lot of problems later on.


Also, you might read up on normalization.


Good luck with your project........

[mariekeb]

Hi ssanfu,

Thank you for taking the time to read my post. However, not sure what you mean by spreadsheet design. I'm making a database for publications from my organisation, and the fields I want to filter contain info on author, publication date, title, etc. These are the collumn headings. That's the right format I believe?

[mariekeb]

Thank you for explaining, Ajax!

[ssanfu]

However, not sure what you mean by spreadsheet design. I'm making a database for publications from my organisation, and the fields I want to filter contain info on author, publication date, title, etc. These are the collumn headings. That's the right format I believe?

"Committing spreadsheet" would be a table design that looks like it was designed for a spreadsheet.
An example of a table designed like a spreadsheet would be to have fields

OrderNumber, CustomerNumber, OrderDate, Qty1, Item1, Product1, Qty2, Item2, Product2, Qty3, Item3, Product3, Qty4, Item4, Product4

To add a 5th product, you would have to add 3 more fields (Qty5, Item5 & product5). This means you have to modify tables, queries, forms, reports and probably VBA code (every time you want to add another product).

In a normalized table design, you would just add another record - no modification required.


This is why I made the comment about "Committing spreadsheet".
In your first post, you have an example of searching 6 fields using the text entered in "Tekst24".

[Field 1] Or [Field 2] Or [Field 3] Or [Field 4] Or [Field 5] Or [Field 6] Like "*" & [Forms]![Query2]![Tekst24] & "*"

If you are searching 6 fields for the same search term, this is generally an indication of a non normalized structure. For example, you have fields "author, publication date, title". If you are looking for an Author, doesn't make sense to search Pub date and Title fields..... but I don't know your table structure....


I am probably wrong...... the search code just looked wrong...

[mariekeb]

Thanks for clarifying, but I do have a normalized table design. I just want people to be able to look for different types of things within a single search box.

In any case, what I have now works, even though it may not be the most elegant solution.

Thanks to both of you for your help, I'm now going to mark this thread as solved

[CJ_London]

just a tip - if you have a large dataset (say 10k+ records) and fields are not indexed it can become quite slow - a few seconds - and even when indexed, the initial * prevents indexes from being used. So I would look at perhaps limiting which fields are searched and remove the first * - if users what to search for something mid string then train them to enter the initial * when required. Also means they learn they can enter something like LY*XY which will find anything that starts with LY and has XY in it as well - makes it a more flexible tool