Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2007-03-22
5
Medium Priority
?
579 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 1000 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 1000 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

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
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.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

688 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