Solved

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

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

896 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

17 Experts available now in Live!

Get 1:1 Help Now