Solved

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

Posted on 2007-03-22
5
501 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
  • 2
  • 2
5 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now