Solved

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

Posted on 2007-03-22
5
512 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
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

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…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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…

815 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

11 Experts available now in Live!

Get 1:1 Help Now