Conditional Values

Boolean Values

A Boolean Type

A value is referred to as Boolean when it is either true or false.  To support Boolean values, Microsoft Access provides a data type named Boolean (but we will not explicitly use that data type in our lessons).

A field is referred to as Boolean if it can assume only one of two values: Yes or No, True or False, On or Off, 0 or non-zero. Such a field is appropriate when you want the user to indicate the state of a value.

To get a Boolean field:

• In the Datasheet View of a table:
  • To create a Boolean field, click Click to Add. In the list that appears, click Yes/No
  • Click the box under Click to Add. On the Ribbon, click Fields. In the Add & Delete section, click Yes/No
• In the Design View, after specifying the name of the field, set its Data Type to Yes/No

Practical Learning: Introducing Boolean Types

1. Start Microsoft Access
2. In the list of files, click Altair Realtors1 from the previous lesson
3. In the Navigation Pane, double-click the Properties table
4. On the table, click any cell under Finished Basement
5. On the Ribbon, click Fields
6. In the Add & Delete section, click Yes/No
7. Type IndoorGarage and press Enter
8. Update the following records:
    

   Property #	IndoorGarage
   524880	Checked
   688364	Checked
   927474	Checked
   682630	Checked

9. Right-click the tab of the table and click Design View
10. In the top part of the window, click FinishedBasement and press Tab
11. Click the arrow of the Data Type combo box and select Yes/No
12. In the bottom part of the window, click Caption and type Is Basement Finished?
13. In the top part of the window, click IndoorGarage
14. In the bottom part of the window, click Caption and type Has Indoor Garage?
15. Right-click the tab of the table and click Datasheet View
16. When asked whether you want to save the table, click Yes
17. Read the other message and click Yes
18. After viewing the records, close the table

Using SQL

To support Boolean values, the SQL provides a data type named BIT. Here are two examples of creating Boolean fields:

CREATE TABLE Contractors
(
    Identifier char(10),
    FullName CHAR(50),
    IsMarried BIT,
    IsFullTime bit
);

The BIT data type is equivalent to the Yes/No data type in Microsoft Access. When performing data entry, if you want a false value, specify it as False, No, Off, or 0. If you want a true value, provide it as True, Yes, On, or any number other than 0.

Options of Boolean Fields

Microsoft Access provides different options to display the value of a Boolean field. By default, a Boolean field displays its value as a check box. As an alternative, you can make it display as a combo box with the values Yes and No or True and False. To choose an alternative:

• In the Datasheet View of a table, click a cell in the Boolean column you want to configure. In the Formatting section of the Ribbon, click the arrow of the Format combo box and select the desired option:

• In the Design View of a table, after setting the Data Type combo box to Yes/No, in the lower section of the table, click the Lookup property page and, on the Display Control combo box, select the desired option:

The default option of a Boolean field is Check Box. If you select Text Box, then the user will be able to type True, False, Yes, No, On, Off, 0, or any number. Once the user types one of these values and presses Enter, Tab, or clicks somewhere else, you can control how the field would display the value. To do this, in the lower section of the window, click the General property page and, in the Format property, select one of the available options:

• If you set the Format property to Yes/No and if the user types 0, the field would display No; if the user types any other number, the field would display Yes
• If you set this property to True/False and if the user types 0, the field would display False; if the user types any other number, positive or negative (but it must be a number) the field would display True

In the Lookup page, if you select Combo Box for the Display Source, the section becomes equipped with various options we reviewed for combo boxes:

As you may know already, you can provide the values in the Row Source field.

A Boolean Control on a Form or Report

After creating a Boolean field on a table, you can display its value in a form or report. As you know already, the default value is a check box. If you create the field as a combo box on a table, if the user should be able to change the value of the field, you can make it a combo (or a list) box. If the control must only display the value as is necessary on a report, you can make it display as a text box. You can also display the control as a text box even if the user can change the value, in which case the user will be able to type 0 for False (or No or Off) or any other number for True (or Yes or On).

Practical Learning: Setting Boolean Options

1. In the Navigation Pane, right-click the Properties form and click Design View
2. In the Tools section of the Ribbon, click Add Existing Fields
3. From the Field List, drag IndoorGarage and drop it on the form
4. Design the form as you see fit

   

5. To preview, right-click the form's tab and click Form View

   

   

   

6. Close the form
7. When asked whether you want to save, click Yes

Logical Constants

True or False

In Boolean algebra, something is considered True when it holds a value. By contrast, if something doesn't hold a value, it is considered non-existent or not worthy of consideration. Such a thing has a value of False, 0, or No.

The comparison for a True or False value is mostly performed on Boolean fields of Yes/No type. If a record has a check mark, the database engine considers that such a field is True. If the check box is not checked, then it holds a False value.

NULL

While the True and False constants are used to find out whether a check box is marked or not, the database provides another constant used to find out whether a value doesn' exist. This can be done using the NULL constant.

When a field holds a value, the value would be considered using a comparison operator. If the field is empty, its value is considered as NULL.

Logical Comparisons

The Equality =

The equality operator is used to compare two values for similarity. The formula to use is:

value1 = value2

If value1 and value2 hold the same value, then the comparison produces a True result. If they hold different values, the comparison produces a False value. This can be illustrated as follows:

Here is an example:

State = "SD"

Not Equal <>

To find out if two values are different or two fields hold different values, you can use the inequality operator which is represented by <>. Its formula is:

value1 <> value2

If the values are different, the comparison produces a True value. If they are different, the comparison produces a False result. This can be illustrated as follows:

Here is an example:

txtLength <> 0.00

The equality (=) and the inequality (<>) operators are opposite each other.

Less Than <

To find out if one value is lower than another, you can use the < operator. Its formula is:

value1 < value2

If value1 holds a value that is lower than that of value2, the comparison produces True. If value1 holds a value that is greater than or similar to that of value2, the comparison produces false. This can be illustrated as follows:

Less Than Or Equal <=

To know whether two fields hold the same value or if one is lower than the other, you can use the "Less Than Or Equal To" operator. It is represented by <=. Its formula is:

value1 <= value2

If both operands (Value1 and Value2) hold the same value, then the comparison produces a True result. If Value1 holds a value that is lower than that of Value2, the comparison still produces a True result. By contrast, if the value of Value1 is higher than that of Value2, the comparison produces a False result. This can be illustrated as follows:

Greater Than >

The > operator is used to find out whether one value is "Greater Than" another. The formula to use is:

value1 > value2

If the first Value is greater than the second, the comparison produces True. Otherwise, the comparison produces False. This can be illustrated as follows:

Note that the > and the <= operators are opposite each other.

Greater Than Or Equal >=

If you want to find out whether two value are the same or the first is greater than the second, you can use the >= operator whose formula is:

value1 >= value2

If both Values are the same, the comparison produces a True result. Similarly, if the first Value is greater than the second, the comparison still produces True. This can be illustrated as follows:

Therefore, < and >= are opposite. This also means that, if you negate a >= comparison, you would get the same result as if you were performing the comparison using the < operator.

Negating a Conditional Operation

To negate a conditional operation, type the NOT operator followed by parentheses. In the parentheses, enter the logical expression. Here is an example:

NOT(txtLength = txtWidth)

The expression in the parentheses would be evalued base on the operator involved. The result would be the opposite. In our example, if both controls hold the same value, the whole operation is False (the opposite to True). If the controls hold different values, the comparison would produce a True result.

The Comparisons Made on Type

The comparison operations can be performed on numbers or text. If the comparison is performed on numbers, it follows the logic of numbers. For example, we learned in elementary school that 12 is greater than 9. If the comparison is performed on strings, the characters of each string are compared based on their positions and based on the language used. In Latin-based languages, the characters of each string are compared from left to right.

If the comparison is made of a check box, the comparison should be made to find out whether the control = True or the control = False.

Logical Operations

Introduction

An operator is referred to as logical if it can be used to compare two values. The result of the comparison produces a True or False value.

The IS Operator

One of the logical operations you can perform on a field is to check whether it is NULL or not. To support this operation, Microsoft Access provides an operator named IS. The formula to use it is:

something IS NULL

The something value can be the name of a field. An example is:

txtRadius IS NULL

In this case, the database engine would check the status (not the value) of a control named txtRadius. If the txtRadius control is NULL, the expression produces a True value.

The NOT Operator

To negate an expression or the existence of a value, you can use the NOT operator. This operator is primarily used to reverse an IS NULL expression. For example, we have learned that False is the opposite of True. In the same way, True is the opposite of False. If you want to compare a value as not being True, the NOT TRUE expression would produce the same result as the False value. For the same reason, the expression Not False is the same as True.

In the same way, you can use the NOT operator to negate an expression. Consider the following expression:

txtRadius IS NOT NULL

This time, the database engine would check the status of a control named txtRadius. If the control holds a value, the comparison would produce a False result (note this very importantly) but if the control doesn't hold a value, then the comparison would produce a True result.

Logical Functions

Introduction

To support logic on actions that can be performed on the values of a database, besides the operators we saw above, Microsoft Access provides various functions.

In the strict sense, because Microsoft Access is not a programming environment, it does not provide a feature called conditional or control statement, which is used to check a condition. Instead, it provides functions you can use to check that a condition is true or false. There are various functions that can be used to create an expression as complex as an algorithm would allow. It is important to note that, although most of these functions perform conditional checking, they do not return the same type of value. For this reason, you should choose the right function. Most of these functions will also be used in combination with other functions as necessary.

Immediate If

To let you create a conditional statement, Microsoft Access provides a function named IIf. The Immediate If function, IIf(), needs three pieces of information in order to accomplish its assignment. The formula to follow is:

IIf(Condition,
    WhatToDoIfConditionIsTrue,
    WhatToDoIfConditionIsFalse) As Boolean

The first action this function performs is to evaluate a condition. The condition can involve an operation or the result of an operation. If the Condition is true, then the function would execute the first expression, which is identified in our syntax as WhatToDoIfConditionIsTrue.

The Condition may lead to only one of two results, True or False, Yes or Not, 0 or not 0. It can be resumed as follows:

IIf(condition,
    ifTrue,
    ifFalse) As Boolean

Here is an example:

=IIf([chkIsMarried]=True,"Is Married","Single")

A Condition could also lead to more than one value but you are interested in only two outcomes. For example, the condition may compare the number of days a clothing item has been displayed to a certain number using a comparison operator. Here is an example:

IIf(txtDaysInStore > 15, 0.20, "")

Another Condition can result in more than two values. For example, imagine a clothing store wants to apply different discount rates to items based on the number of days the items have been displayed in the store. To apply such a scenario, you can call an IIf() function inside another. This is referred to as nesting.

To nest an IIf() function in another, in place of the ifFalse placeholder, call another instance of IIf(). This would be done as follows:

IIf(condition,
    ifTrue,
    IIf(condition-else,
        if-else-true,
        if-else-false)) As Boolean

In the same way, you can nest an IIf() function in any other IIf() function. All you have to do is to replace an IIf() call with an if-false expression.

Practical Learning: Using IIf

1. On the Ribbon, click File and click Open
2. In the list of files, click FunDS1
3. In the Navigation Pane, right-click the StoreItems form and click Design View
4. On the form, click the text box below the Rate label
5. In the Property Sheet, click Data and, in the Control Source, type:
   

   =IIf([txtDaysInStore]>45,0.5,IIf([txtDaysInStore]>28,0.35,IIf([txtDaysInStore]>15,0.2,"")))

6. Save the form

Value Checking Functions

Non-Zero Checking

When studying numeric types, we reviewed different functions that could be used to convert a value or an expression to the desired type. We saw CByte, CInt, CSng, CDbl, and CCur. Before performing any operation on the value held by a field, you should first convert it to the appropriate type. The conversion functions we mentioned assume that the field contains a value. Unfortunately, this is not always the case. Microsoft Access provides the Nz() function that can be used to check whether a field contains a value, whether the field is empty, or is null. The syntax of this function is:

Nz(Value, ValueIfNull) AS Appropriate Type

The first argument, Value is required. The second argument is optional. The pseudo-code for this function is:

If the field contains a value
    Return that value
Otherwise
    Return 0

In other words, this function considers the value provided by the Value argument. This could be an expression or the name of a field (or control) that is involved in a calculation. If the Value argument or the passed field is empty, this function returns 0. If the field contains a value, this function returns it. The beauty of this function is that it provides a valuable safeguard for an operation.

The second argument is optional and it would be used as the return value if the Value argument were null.

Practical Learning: Using the Nz() Function

1. On the Ribbon, click File and click Open
2. In the list of files, click College Park Auto-Repair1 from the previous lesson
3. In the Navigation Pane, right-click sfJobsPerformed and click Design View
4. In the Controls section of the Ribbon, click the Text Box control and click the Form Footer section of the form
5. In the Property Sheet, click the All tab and change the following characteristics:
   Name: txtCosts
   Control Source: =Sum(Nz(Cost))

   

6. On the form, click the Form Footer bar
7. In the Property Sheet, double-click Visible to set it to No
8. Save and close the sub-form
9. In the Navigation Pane, right-click sfPartsUsed and click Design View
10. From the Controls section of the Ribbon, add a text box to the Detail section of the form
11. Move its label to the Form Header section and set its caption as Sub-Total
12. Click the text box and change its characteristics in the Property Sheet as follows:
    Name: txtSubTotal
    Control Source: =Nz([UnitPrice])*Nz([Quantity])
13. In the Controls section of the Ribbon, click the Text Box control and click the Form Footer section of the form
14. In the Property Sheet, change the following characteristics:
    Name: txtSubTotals
    Control Source: =Nz(Sum(Nz([UnitPrice])*Nz([Quantity])))

    

15. On the form, click the Form Footer bar
16. In the Property Sheet, double-click Visible to set it to No
17. Save and close the sub-form
18. In the Navigation Pane, right-click the RepairOrders form and click Design View
19. On the form, double-click the Jobs Performed tab to select it and access its properties
20. In the Property Sheet, click Format, click Page Index and type 0
21. From the Controls section of the Ribbon, add a text box to the form
22. In the Property Sheet, change its characteristics as follows:
    Name: txtTotalParts
    Control Source: =Nz([sfPartsUsed].Form!txtSubTotals)
    Format: Fixed
23. Change the caption of the accompanying label to Total Parts:
24. From the Controls section of the Ribbon, add a text box to the form
25. In the Property Sheet, change its characteristics as follows:
    Name: txtTotalLabor
    Control Source: =Nz([sfJobsPerformed].[Form]![txtCosts])
    Format: Fixed
26. Change the caption of the accompanying label to Total Labor:
27. From the Controls section of the Ribbon, add a text box to the form
28. In the Property Sheet, change its characteristics as follows:
    Name: txtTaxAmount
    Control Source: =CLng(CCur(Nz([txtTotalParts])+Nz([txtTotalLabor]))*CDbl([TaxRate])*100)/100
    Format: Fixed
29. Change the caption of the accompanying label to Tax Amount:
30. From the Controls section of the Ribbon, add a text box to the form
31. In the Property Sheet, change its characteristics as follows:
    Name: txtOrderTotal
    Control Source: =CCur(Nz([txtTotalParts]))+CCur(Nz([txtTotalLabor]))+CCur(Nz([txtTaxAmount]))
    Format: Fixed
32. Change the caption of the accompanying label to Order Total:
33. Format the controls to appear like the others:

    

34. Save and close the form

Field Emptiness

A field or control is empty if it currently holds no value. To let you get this information, Microsoft Access provides a a function named IsEmpty. Its syntax is:

IsEmpty(Value) As Boolean

This function checks its argument, which could be a field or an expression. If it is a field and the field is empty, this function returns True. If the field is not empty, the IsEmpty() function returns False.

Nullity Checking

A problem you may encounter when involving a field in an operation is whether the field has never contained a value. This operation is sometimes confused with that of checking whether a field is empty. Here is the difference (it is important to understand this because it is used in many other environments, including Microsoft Visual Basic and Microsoft SQL Server):

• Imagine a field is used for a first name. If the user comes to a new record (such as for the first time), the field for the first name may be empty (if it doesn't have a default value). In this case, the field is null: it is not empty because it has never contained anything. If the user types something, and then deletes it, the field is not considered null anymore: it has become empty
• Imagine a field is used for a first name and the field displays Paul. If the user comes to that record, the field is not empty, it already contains a value. If the user clicks in the field and deletes the value, the field becomes empty. It is not null

To let you check whether a field (or a value or a control) is null, Microsoft Access provides a function named IsNull. Its syntax is:

IsNull(value) As Boolean

Also used on fields, the IsNull() function checks the state of a field (remember, this functions does not check whether a field is empty or not; it checks whether the field has never contained a value). If the field it null, this function returns True. If the field is not null, this function returns False.

Numerical Checking

Before involving a value, a field, or a control to an arithmetic operation, you should check that it contains a valid number. To let you perform this checking, Microsoft Access provides a function named IsNumeric. Its syntax is:

IsNumeric(value) As Boolean

The IsNumeric() function returns True if its argument is a valid number. Otherwise, it returns False.

Practical Learning: Checking a Numeric Value

1. On the Ribbon, click File and click Open
2. In the list of files, click FunDS1
3. In the Navigation Pane, right-click the StoreItems form and click Design View
4. On the form, click the text box below the Amount label
5. In the Property Sheet, click Data and, in the Control Source, change the expression to:
   

   =IIf(IsNumeric([txtDiscountRate]),[UnitPrice]*[txtDiscountRate],"")

6. On the form, click the text box below the After Discount label
7. In the Control Source of the Property Sheet, change the expression to:
   

   =IIf(IsNumeric([txtDiscountRate]),[UnitPrice]-[txtDiscountAmount],"")

8. Close the form
9. When asked whether you want to save, click Yes
10. Close Microsoft Access