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

Plucking Things from the Middle of the Pack:  Range Queries

Back in my youth, we had something called tape recorders. These devices reproduced sound. You recorded voice or music on a reel-to-reel tape. Today, this seems like ancient technology – and it is.
 
Although a wondrous invention and the source of much fun and entertainment, it had a significant drawback. If you wanted something in the middle of the tape you had to move the tape forward to get there. It was tough to tell when you were where you wanted to be. You had to listen a bit and then move the back and forth to get to the right spot. The whole process was a pain.
 
In a similar way, sometimes \you will have a list of data that is sorted in some fashion and the data you are interested in is neither at the beginning or end, but rather someplace in the middle. In this month’s Wizard, I will show you a technique that will demonstrate how to pluck something out of the middle of a list – the range query.
 

The Logic and Syntax

 
Let’s say that we have the following list of names sorted by Last Name:
 
First Name Last Name
 Claire  Annette
 Hy  Ball
 Robin  Banks
 Candy  Barr
 Barry  Cade
 Chris  Cross
 Holly  Day
 Iona  Frisbee
 May  Furst
 Jack  Hammer
 Pierce  Hart
 Dee  Licious
 Carrie  Oakey
 Holly  Wood
 Adam  Zapel
 
What we want is a list of those names where the Last Name starts with any letter from D to H. To solve this problem in a query, we could use something on the order of the following:  
[
Last Name] like D* or [Last Name] like E* or [Last Name] like F* or …
 
But this would be really tedious and annoying to write.
 
We could also write a function using a select case statement, and then call the function from the query. But there is a much easier way.
 
For our criteria in the last name column, we can use: 
Like "[d-h]*"

 
This will give us only those names where the last name starts with any letter from D to H.
 

Conclusion

 
This technique allowed us to pluck things out of the middle of the sorted list. Although it is a simple technique; when you have the need to use it, it can save a lot of work. This technique is most helpful in dealing with text rather than numbers or dates.
 
For anything that is numerical you can use the “>” and “<” symbols to return something similar.

 

Tip of the Month — An Approach to Solve Tricky Query Problems

Sometimes when you are creating a query and you think your syntax is correct, but you find that you don't get back the records you expect, the issue could be behind the scenes rather than in front of your eyes.
 
An easy way to take a look at what's really going on at the SQL level is to change the view SQL.
 
By doing this, you can see the actual code that creates what you see in the design window. By peeking at the real code you can sometimes decipher a logic or syntax error. For more information about this technique see the June 2005 Wizard: "What's Really Happening with Queries -- Pay No Attention to the Man Behind the Curtain."

 
Quick Links:

Custom Software Home

Access Wizard Archives

Our Services


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