You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Today’s Power Tip comes from Bob Larson of BTAB Development. Bob is a former Access MVP (2008-2009, 2009-2010), and offers a host of tips and code snippets on his site.
If you have a long list of items to exclude from a query, and you don’t want to type a big IN statement in the query to exclude them, you can use a table to do the exclusions instead. I have posted a short screenshot-based “Quick Tutorial” on my site that shows you how:
Seems like a long way around when you could just use the query in the first place.
Bonnie - how would you use a query to make up an arbitrary list to be used as filter? Besides, a frustrated join has the potential to be optimized whereas a NOT IN() cannot be.
Instead, I'd use ListBox to select unwanted records and then dynamically create the IN part of the query for the form/report. I could even store IDs of selected items in an INI-file to be used later. No extra tables needed...
And so let's say you have been given a list of companies to exclude in an Excel workbook and there are 200 entries. Are you goint to spend the time to manually enter all of those in a NOT IN statement or go select all 200 of them in a listbox? Not practical. There are plenty of times when this is very useful.
Oh, and also just another thing you can do - at work, we typically can have a lot of large queries. We will use this method with a subquery which we attach into things to either select what we want or exclude what we want using a query that has been created. Many times we use this because we have some memo fields that are needed or perhaps we want to have an outer join with another query and, of course, you cannot have criteria on the fields that are on the many side of an outer join.
The example Bob posted is perhaps too simple to be a hit with this audience, but we use the same basic idea all the time. For complicated filtering scenarios it's very efficient. We often create a permanent local table for storing lists of ID's to be excluded via left outer joins; a delete query clears the data from that 'temp' table and an append query repopulates it. Sometimes the records to excluded are 'hand picked' via a listbox; more often they're generated by a code routine that relies on form based controls to supply criteria for the filter. Bob's later posts here in which he cites the real world scenarios for this approach line up very well with our own approach.
Great sample, great tips to build other more complex tasks, thanks for sharing!
This is one of the issues I resolve with a "tag" checkbox - a field I check for including or excluding records that don't match some other logical pattern. For instance, I may get a number of newsletters returned by the Post Office - I do the necessary research to update the addresses (if possible), check the "tag" field, and run a mailing query on just the "tagged" records. After processing those records, I run an "untag" query.