|
Make a Decision on the Fly - The Immediate IF
Friends and long time Wizard Readers know
that I am
a Geocaching enthusiast. For the uninitiated,
Geocaching uses satellites orbiting the earth that
send signals to GPS devices so you can find
treasures in the woods. Walking in the wild, while
enjoying a beautiful day, is a treat. It's a hobby that
takes me to many places I would never find on my
own.
Although GPS devices point the way to your
destination, when you are walking in the woods, the
best way to get from point A to point B is almost never
a straight line. Instead, it makes sense to follow pre-
established paths until you get close, at which point
you begin to engage in the time-honored practice of
bush whacking, where you make a beeline for the
target by thrashing through bushes and brambles
while hopefully avoiding the dreaded Lyme tick.
While making your way to a cache in the woods,
frequently a trail will split in two. At that point, you have
to make an immediate decision: left or right. Choosing
correctly will ease your way to the destination;
choosing incorrectly will cost you time and, potentially,
will create confusion.
In a similar fashion, Access also provides a way of
making an immediate decision - the Immediate
If. In
this case, it is a technique that is easy to use, requires
no code, and is relatively simple to understand.
| The Basics |
 |
|
The Immediate If is used in queries for making
a clear
decision; it can return one of
two results.
The syntax is straight forward:
iif(condition, what to return if the condition is true,
what to return if the condition is false)
Note that the syntax is iif, not if. I believe that it's iif
because it's an immediate decision, and because it
helps reduce conflict with the if statement used
in VBA
coding, which also runs in Access.
|
| A Picture is Worth 1000 Words |
|
|
Let's say that we have a list of people with their
birthdays, and we want to separate them into two
groups, elderly and sprightly. We'll declare them
elderly if they were born before 1945, otherwise we
declare them as sprightly.
Now before the Politically Correct Police start emailing
me, I know that somebody born before 1945 is not
necessarily elderly, and being born after that date
does not make you sprightly. I also know that I should
be calling senior citizens something along the lines
of "age-enhanced" or "mature," but this is a computer
example, not a social statement.
Take a look at the picture below.
In this query, we have a table of names and birthdays.
The last column contains our immediate if statement.
In this case I'm looking at their birthday (the DOB field)
and saying if their birthday is before January 1, 1945,
then they are elderly; otherwise, sprightly.
A couple of things to know about the syntax: Because I
am using a date in my iif statement, it must be
enclosed within # signs. Also, because I am returning
words to the query, I must enclose them in quotes.
When we run the query, we get the following:
Note that the only folks who are labeled as Elderly are
April Schauer, Brock Lee, and Roy Rogers, all born
before 1945.
If I wanted to, I could do all the usual things with a
query like this, such as sort by age, or return only
those who are elderly.
|
| Implications and Limitations |
|
|
This very simple technique can be used in any query
where you have to make a yes/no decision; it is not
limited by the type of data. You just have to correctly
establish the logic and then you're off and running.
Furthermore, you can return whatever you want. I
wanted words to come back, but if I wanted to bring
back two different levels of monetary payment, or "yes"
rather than "elderly," I could certainly do that. This is a
powerful tool.
This technique does have its limitations though. It is
only good to test a yes/no condition; it is not terribly
helpful if you want to split your data into more than two
groups. Although you could use the technique by
nesting one immediate if inside another immediate if,
I would not recommend that approach. It gets too
confusing and difficult to debug. You would be much
better off calling some code with a Select Case
construct or using the switch function.
So the next time you have a query where you need to
separate the sheep from the goats, consider the
Immediate If.
|
|
Tip of the Month: Get Application Information from a Query |
|
|
|
In January 2010, I demonstrated how to get the path to
your application by using a code trick: Debug.Print
Application.
CodeProject.FullName
Clever user Randall Krupa suggested a way to get that
and more information from a query as well.
In a query include any of the following in the
field row of the grid:
CurrentProject.Path
- which will give you the full path of
your application
CurrentProject.FullName - which will give you the path
plus the full name of the application
Left(CurrentProject.
Name,Len(CurrentProject.
Name)-
4) - which will give you the name of your project
without the path. (Note that if you're using Access 2007
or later you'll want to change the 4 to a 5 or a 7.)
Nifty tricks - Thanks Randall.
If you have any tricks you'd like to share, send them
along.
|
|
