Use Access form to filter data (include and exclude values) in query
Posted on 2007-03-22
Hi! I am trying to create a query tool in a form so users can easily select their selection criteria and return rows from a query. Once they have selected their criteria, they press a button to run the query. There will be several different fields they can choose but I am giving an example of one field below.
Example of the data rows for field Names:
Smith, Joe- Jones, Mary- White, Bob-
Smith, Joe– White, Bob-
Jones, Mary– White, Bob-
The users get a drop-down of the individual names to select one at a time. So, if they select “Smith, Joe”, I store "*Smith, Joe*". If they select “White, Bob”, I add that to the selection and store “*Smith, Joe*” or “*White, Bob*” in txtNameCriteria, which is the form field.
Here’s how I use that field in the query in criteria.
It works if one name is selected or no names are selected, in which case I want to show all rows. If they select Smith, Joe, they see the first 3 rows. However, it doesn’t work if more than one name is selected. If they select “Jones, Mary” and “White, Bob”, rows 1, 3 and 4 should be returned. Instead, the query returns no rows.
I also need to allow them to exclude names. For example, if they say “Smith, Joe”/include (I am giving them a drop-down to say include or exclude), and “White, Bob”/exclude, only row 2 should be returned because it has Smith, Joe and not White, Bob (in other words, find the rows where Bob White is there but Joe Smith is not).
I will be doing this type of logic for many other fields so I hope that, once I get one working, I can apply that logic to the other fields.
I hope this makes sense. Thanks for your help.