Categories
Blog

Conditional Functions in Microsoft Access

Microsoft Access offers the more advanced users a way around some of the most time consuming and repetitive actions by using conditional functions. Conditional functions help you reduce how much data you have to store by telling Access how to calculate or compare information in specific fields so that you don’t’ have to create an entirely new row or column of information.

For example, if you are a teacher and you are storing students’ grades in an online database, you might feel compelled to add a row or column to show whether a grade is passing or failing. This would be redundant since the numeric value of the grade already shows this. However, there will be times when you want to run reports on the number of people who passed or failed tests over the course of a semester, so what you want is not a report with numbers that you will have to translate into pass or fail.

Before getting into what you can and cannot do with conditional functions, you need to understand what they are. Once you have a basic understanding of them, we’ll take a look at two of the most common conditional functions: IIf and Switch.

What Is a Conditional Function?

Essentially all databases have the option to use conditional functions. Conditional functions can be as simple as a Boolean value where a person enters data on a form and is only able to check one out of two boxes. The conditional Boolean will automatically

grey out the other value, so that the answer is only Yes or No to the question on the form. It can also make the field required so that the form cannot be submitted until one of the two conditions listed is checked. This is commonly used for official forms to ensure that all of the required information is supplied before a form can be saved. For more advanced users, you can add a conditional function to send an email to certain employees every time a new account is opened (a particular database form is used to create a new account) and the amount of the account exceeds a certain amount, such as $499. To add a conditional function to Access, you will need to have a basic understanding of SQL to create the expressions.

When Would You Use a Conditional Function?

Conditional functions give you the controls to simplify working in Access. There are many things that can be automated, keeping everyone on the same page without requiring a member of staff to constantly monitor the database. Conditional functions give you the power to make work a lot easier if you have the time to learn how to work with them.

The best way to think of conditional functions is as a method of automating a process. If there is an action that is repetitive, conditional functions can take care of it so that you can focus on more the more important elements of the process. Conditional functions can keep users from entering conflicting information. There are many different uses for conditional functions, so the best way to learn how to use conditional functions to is learn about each of them. Once you understand what they do, you can determine how best to apply them to different situations.

There are a number of different conditional functions, but the two most common are IIf and Switch conditional functions.

When and How to Use the IIF Conditional Function

IIF stands for the Immediate If function and it can be combined with other conditional functions, like the Boolean to accomplish an immediate reaction from Access if a certain action is taken by the user. The following is a brief example of what the IIF condition needs.

IIf (Checkbox Condition

      ExecuteIfConditionMet

      ExecuteIfConditionNotMet) As Boolean

When the user’s action meets the first criteria, Access will execute the first condition; otherwise the second condition is triggered. You can set the condition to work with several different values, but IIf only deals with one condition at a time. There are more complex and elaborate expressions that execute actions based on multiple conditions. For now, this should be enough code to work with setting the conditions up is not something that can be done with a couple of clicks of the button.

Since most people will be working with forms when setting up conditions (after all it helps to streamline the data entry process), the following instructions demonstrate how to create an IIf condition on a form. IIf conditions can be set for reports by using the same instructions.

  1. Open a form.
  2. Double click on the field where you want to add the IIf condition. The Property Sheet menu will appear.
  3. Click on the Event tab of the Property Sheet menu.
  4. Select the condition when you want to apply the IIf condition. For example, After Update will trigger the action after the user updates the field. There are over a dozen possible conditions that can be applied. You will have time to experiment with them later once you understand how to apply the condition.
  5. Click on the ellipses button (to the right of the drop down menu). The Chooser Builder will open.
  6. Click on Expression Builder in the Chooser Builder window.
  7. Under the Expression Elements menu, click on Functions > Built-In Functions.
  8. Under Expression Values, scroll down and click on IIf. The following text will appear in the text box: “IIf (<<expression>>,<<truepart>>,falsepart>>)” This is the text that you will update to meet the IIf condition.
  9. Click OK when you are done updating the text.

Take some time to try different scenarios.

When and How to Use the Switch Conditional Function

The switch condition is one that can work with multiple conditions, making it more diverse than the IIf condition, but also much more complex. The following is an example of switch condition text.

Switch (Expression1, Execute if the expression is true,

      Expression2, Execute if the expression is true,

      Expression3, Execute if the expression is true,

      True, Execute if no expressions are true) As Value

You can add as many expressions as you need to add, which can make automation considerably easier for users. The last may not be needed, but it will guarantee that Access does not get hung up if none of the expressions are true.

Again, we will focus on forms, although the same steps apply for reports.

  1. Open a form.
  2. Double click on the field where you want to add the IIf condition. The Property Sheet menu will appear.
  3. Click on the Event tab of the Property Sheet menu.
  4. Select the condition when you want to apply the IIf condition. For example, After Update will trigger the action after the user updates the field. There are over a dozen possible conditions that can be applied. You will have time to experiment with them later once you understand how to apply the condition.
  5. Click on the ellipses button (to the right of the drop down menu). The Chooser Builder will open.
  6. Click on Expression Builder in the Chooser Builder window.
  7. Under the Expression Elements menu, click on Functions > Built-In Functions.
  8. Under Expression Values, scroll down and click on Switch. The following text will appear in the text box: “Switch(«expr-|0», «value-|0»)” This is the text that you will update to meet the switch condition.
  9. Click OK when you are done updating the text.

Take some time to try the different expressions, starting with two, and then adding more as you get comfortable with the switch condition.

Leave a Reply

Your email address will not be published. Required fields are marked *