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

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.

In this Issue
  • Tip of the Month: Get Application Information from a Query
  • The Basics
  • A Picture is Worth 1000 Words
  • Implications and Limitations

  • 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.

    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

    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.

    Quick Links...

    Custom Software Home

    Access Wizard Archives

    Our Services

    Join our mailing list!

    Forward email

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