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.
