The Access Wizard Newsletter Tips, Tricks, and Traps for Access Users and Developers
April 2011

Finding a Needle in a Haystack: Don't Do it the Obvious Way

Ever try to find a needle in a haystack? Me neither, but if I had to tackle that task, I wouldn't look forward to it. Your immediate inclination might be to dig in and start looking, turning over each straw of hay until, by some more miraculous stroke of good luck or star alignment, you finally find the needle.

Wouldn't it be better to sit back and think a while before jumping in? Just because a task seems monumental doesn't mean it has be. Perhaps there's another less obvious method that would get the job done in a reasonable fashion. Super-duper magnets come to mind.

This month, we'll look at a similar tool for finding a needle in a haystack. But this time, the haystack is Access and the needle is a specific query buried in a list of similarly named queries.

In this Issue
  • Tip of the Month - How to Get a Fields Population
  • The Real-Life Problem
  • The Solution

  • The Real-Life Problem

    I recently was helping a client debug some fairly complex queries. He had queries based on queries based on queries.

    As we started to debug one of the queries, we saw that the query in question was based on another query called qryGetAllOpenCasesStatusABA.

    We went to the database view of all queries and, much to my surprise, there were literally hundreds of queries, many with names similar to the one we were seeking. There was about half a screen of queries that started with GetAllOpenCasesStatus followed by a series of letters. When he opened the query we were interested in, I noticed that he chose one that ended in AAB rather than ABA. Since the query was similar, my customer was not aware he had made a mistake, I asked him to back up and check the query. He realized that he had picked the wrong one. Something that was almost bound to happen, given the number of queries and the similarity of their names.

    The Solution

    There's an easy way to make sure that you get to a query based on another query.

    Take a look at the picture below. The design view of this query I've created is based on another query. Imagine, if you will, that the query name that is embedded in this query is long and complex, and what I want to do is to avoid needing to remember the name and then finding it in a list.

    The first step is to bring any field from the embedded query, in this case qsumActivityCount, into the bottom half of the design window. The row labeled "table" contains the name of my embedded query. You can easily copy that query name onto your clipboard by highlighting it, and pressing the control and C keys simultaneously.

    So at this point, you have the name of the query on your clipboard. You're halfway there: You have the name of the query on your clipboard, but you can't use the clipboard to open the query to look at it yet, which is your real goal.

    To accomplish this last piece of the puzzle, you have to open the query programmatically. That's not as hard as it sounds. Press Control-G. This will pop you into a section of the coding interface called the Immediate window; it's a place where you can run a line of code just by typing it in. Once in that window, type the following:


    followed by the contents of your clipboard (press Control-V, or choose edit paste from the menu) with leading and trailing quotes as in the following:

    docmd.OpenQuery "qsumActivityCount"

    Next hit the enter key. The query will run and show you the data it returns. From there, you can easily open it in design view to begin to understand the logic behind the query. If you want to make things just a bit faster and go directly to the design view simply add a comma followed by acViewDesign, as follows:

    docmd.OpenQuery "qsumActivityCount",acViewDesign

    This time when you hit return, you will pop directly into the query's design view. From there, you can do your detective work knowing that you have the right query, and you will have avoided the hassle of trying to visually hunt down a query that looks like many of its neighbors. You will have found an easy way to get to that elusive needle in that very large haystack.

    Tip of the Month - How to Get a Fields Population

    If you have a big table, it's likely that certain fields are populated for some records and not for other records. Sometimes it's helpful to know how many records are populated in a specific field within a large table. You could, of course, open the table sort by the field, and then scroll down to see how many records are populated.

    You can also be a little bit more clever and filter out blank records, then just look at the record count of the bottom of the screen. If you're not sure what filtering out is, I'll cover that subject in a future Wizard.

    But say you had to do this for each field in a table with 50 fields. This would become very tiresome very quickly. There's a technique you can use that's quite simple. Do dcount of the field, but omit the criteria. This has the syntax:

    =dcount(field name, table name)

    This will bring back the count of records in your field that have something in them. Using this technique, which you could incorporate into a small program, would then give a record count by field with much less work

    Quick Links...

    Custom Software Home

    Access Wizard Archives

    Our Services

    Join our mailing list!

    Custom Software | Copyright Custom Software All Rights Reserved | Westford | MA | 01886