Solved

Use Access form to filter data (include and exclude values) in query

Posted on 2007-03-22
5
544 Views
Last Modified: 2012-08-14
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-
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.  
Like IIf(nz([Forms]![frmtest]![txtNameCriteria])<>"",nz([Forms]![frmtest]![txtNameCriteria]),[names])

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.
Alexis
0
Comment
Question by:alexisbr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 18772718
What does your [Forms]![frmtest]![txtNameCriteria]),[names] field contain when you return incorrect results? Those values should be ORed together:

WHERE SomeField LIKE Smith,Bob OR SomeField LIKE Smith,Jane etc etc

To exclude, just use OR NOT LIKE

0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 18773194
see this link.  It has a free demo mdb with source code, named Flexible Query, which shows various aspects of a working flexible query facility like you are trying to build..
                                      http://www.utterangel.com/pages/access_downloads.aspx#22
0
 

Author Comment

by:alexisbr
ID: 18773813
LSMConsulting,
I see what you are saying but where do I put the WHERE clause?  The selections are being used against a query, not a form.  If I could do a filter on the query like I would on a form, I see how I could use your logic.  

PuppyDogBuddy,
Thanks for the link.  I am reviewing it now.

Alexis
0
 
LVL 38

Assisted Solution

by:puppydogbuddy
puppydogbuddy earned 250 total points
ID: 18774333
Alexis,
Here are links to two more sample query facilities.  These demo mdb's are provided with source code, and are essentially free ($1 voluntary donation thru PayPal for each file dowloaded is requested to help support the web site).

This form illustrates how to create a relatively flexible inter-active query facility for an end-user. It allows you to choose the table, then shows the fields available for that table and then values available for the fields.  The queries limit the user to three criteria.
http://www.rogersaccesslibrary.com/download3.asp?SampleName=CreateQueries4.mdb

This set of samples illustrate how to create a flexible query generator for your users. It is similar to CreateQueries4.mdb, but no longer limits the query to 3 criteria.
http://www.rogersaccesslibrary.com/download3.asp?SampleName=CreateQueries5.mdb

Hope this helps.

PDB
0
 

Author Comment

by:alexisbr
ID: 18791659
PuppyDogBuddy:  Although the way my logic is set up is very different from the samples, they gave me some great ideas, especially on creating a temporary local database in multi-user mode.  I will need to implement that with my query logic too.

LSMConsulting:  I figured out that you must have meant that I should use the WHERE clause in a querydef.  I hadn't even thought of using a querydef but that is working out so far and hasresolved my issue with handling excludes.

Thanks to both of you for your help.
Regards,
Alexis
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question