Database Conditional Values

Boolean Values

The Boolean Data Type

A value is referred to as Boolean when it is either true or false. In other words, the value can only be one or the other, never both, and not any other value. To support Boolean values, Microsoft Access provides a data type named Boolean (but we will not explicitly use that data type in our lessons).

Practical Learning: Introducing Boolean Types

1. Start Microsoft Access and, to create a new database, click Blank Database
2. Set the File Name to Real Estate4 and click Create
3. Double-click ID, type PropertyID, and press Enter
4. Right-click the Table1 tab and click Design View
5. Set the name to Properties and click OK
6. Click the empty field under PropertyID and type Property #
7. Click the empty field under Property # and type p
8. For its Data Type, select Lookup Wizard...
9. In the first page of the wizard, select the second ration button and click Next
10. Click under Col1 and type Single Family
11. Press the down arrow key and type Townhouse
12. Press the down arrow key and type Condominium
13. Press the down arrow key and type Unknown
14. Click Next
15. Set the lookup column label to Property Type and click Finish
16. Change the following properties:
    Field Size: 40
    Default Value: "Unknown"
17. In the top section of the table, click the first empty field under Property Type and type p
18. For its Data Type, select Lookup Wizard...
19. In the first page of the wizard, select the second ration button and click Next
20. Click under Col1 and create the following items:
     

    Unknown

    Excellent

    Good Condition

    Needs Some Repair

    Bad Shape

21. Click Next
22. Set the lookup column label to Condition and press Enter
23. Change the following properties:
    Field Size: 40
    Default Value: "Unknown"
24. Complete the list of fields with the following (ignore any information that is not provided):
     

    Field Name	Data Type	Field Size	Input Mask	Default Value	Other
    PropertyID
    Property #	Number
    Property Type		20		Unknown
    Condition		20		Unknown
    Address		100
    City		50
    State		2	>LL	MD
    ZIP Code		12	00000\-9999;0;_
    Bedrooms	Number	Byte
    Bathrooms	Number	Single
    Stories	Number	Integer
    Year Built	Number	Integer
    Market Value	Number	Double			Format: Currency

25. Save the table
26. To change its view, in the lower-right section, click the Datasheet View button

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

There are various ways you can set up a field for Boolean values:

• While the table is in the Datasheet View:
  • 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: Using the Boolean Data Type

1. If the Field Templates window is not available, on the Ribbon, click Datasheet and, in the Fields & Columns section, click New Field.
   the Field Templates, from the Basic Fields section, drag Checkbox and drop it on the right side of Stories
2. On the table, double-click the newly added Checkbox column header and type Has Finished Basement?
3. Right-click the Properties tab and click Design View
4. In the top section of the table, right-click Year Built and click Insert Rows
5. Type With Indoor Garage
6. Press Tab and type y
7. Save and close the table

Microsoft Access provides different options to support various ways of expressing a Boolean field:

• If you are working in the Datasheet View, 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:

• If you are working in the Design View, 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 one of the available options:

The most classic way consists of showing the value of a Boolean field is with a check box:

With this layout, the user can either click the check box or give it focus and press the space bar. Alternatively, if you prefer, you can let the user type a value.

When configuring the field in the Design View of the table, if you select Combo Box, many other options would be presented to you:

The default option 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 table in Design View, 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

Practical Learning: Setting Boolean Options

1. In the top section of the table, click Has a Finished Basement?
2. In the lower section of the table, click Lookup
3. Click Display Control, then click the arrow of its combo box and select Combo Box
4. Click the General tab
5. Click the arrow of the Format combo box and select Yes/No
6. Right-click the Properties tab and click Datasheet View
7. When asked to save the table, click Yes
8. Enter a few records
9. Close the table

Logical Constants

A database environment like Microsoft Access provides operators you can use to perform logical operations on values of a database. The operators used are called logical operators because they are used to perform comparisons that produce a result of true or false.

In Boolean algebra, something is considered True when it holds a value. The value is also considered as 1 or Yes. By contrast, if something doesn't hold a value, it is considered non-existent and not worthy of consideration. Such a thing has a value of False, 0, or No. In reality, everything in your database has a value, as far as the database engine is concerned. To retrieve such a value, you can just find out if the value of a field is existent or not.

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.

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 field is empty. This can be done using the NULL constant.

When a field holds a value, the value would be considered using the comparison operators we will learn shortly. If the field is empty, it holds a value of NULL.

Logical Operations

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 factor can be a placeholder for the name of a field. An example would be

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.

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 such as the one we saw above. 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 produces a True result.

1. Open the College Park Auto Repair1 database you started in Lesson 15 and continued in Lesson 20
2. In the Navigation Pane, under Tables, double-click RepairOrders to open it in the Datasheet View
3. On the table, click the cell under Customer Name
4. On the Ribbon, click Fields
5. In the Field Validation section, click Validation and click Record Validation Rule
6. In the Expression Elements list, make sure RepairOrders is selected.
   In the Expression Categories list, double-click CustomerName
7. Type IS NOT NULL
    
   
8. Click OK
9. In the Field Validation section, click Validation and click Record Validation Message
10. Type Make sure you specify the name of the customer
     
    
11. Click OK
12. Close the table

Logical Functions

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.

One of the most regularly used functions to perform conditional statements is called IIf. The Immediate If function, IIf(), needs three pieces of information in order to accomplish its assignment. The formula used is:

IIf(Condition,
    WhatToDoIfConditionIsTrue,
    WhatToDoIfConditionIsFalse) As Boolean

The first action this function performs is to evaluate a condition. This condition could 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 could lead to only one of two results. For example, the Condition could consist of checking whether an employee is married or not. If the field is a combo box, it might have only a Yes and a No values.

A Condition could also result in more than two values. For example, suppose a combo box holds the book categories of a database. Such a combo box could display values such as History, Entertainment, Biographies, Computers, Computer Programming, Geography, and Artificial Intelligence. When a field can hold various values, the Condition you specify as the first item of your IIf() function would be checked. If it produces a true result, the WhatToDoIfConditionIsTrue statement would be executed. If the Condition produces a false result, the WhatToDoIfConditionIsFalse statement would be executed. This is important to keep in mind: the IIf() function checks only one condition. If you want to check more than one condition, you would have to write a more elaborate expression.

Practical Learning: Using IIf

1. In the Navigation Pane, under Forms, right-click RepairOrders and click Design View
2. Click Jobs Performed
3. Click the JobPerformed1 text box
4. In the Property Sheet, click the Event tab
5. Click On Lost Focus and type
    
   =IIf([JobPerformed1] Is Null, MsgBox("Specify at least one of the jobs that were performed on the car"),"")
6. Save the form

Since Microsoft Access doesn't inherently provide a programming environment, it relies on logical functions to take care of this aspect. One of the functions used to perform looping operations is called Choose. The Choose() function is one of those that can test a condition and provide alternatives. The Choose() function works like nested conditions. It tests for a condition and provides different outcomes depending on the result of the test. Its syntax is:

Choose(Condition, Outcome1, Outcome2, Outcome_n) As Number

The first argument of this function is the condition that should be tested. It should provide a natural number. After this test, the Condition may evaluate to 1, 2, 3, or more options. Each outcome is then dealt with. The first, Outcome1, would be used if the Condition produces 1. The second, Outcome2, would be used if Condition produces 2, etc.

We saw that the IIf() function was used to check a condition and could perform one of two statements depending on the result of the condition. In some expressions, there will be more than one condition to check. Although you can nest IIf() functions to create a complex expression, Microsoft Access provides another function that can perform this task. The function is called Switch and its syntax is:

Switch(Expression1, What To Do If Expression1 Is True,
       Expression2, What To Do If Expression2 Is True,
       Expression_n, What To Do If Expression_n Is True) As Some Value

Unlike IIf(), the Switch() function does not take a fixed number of arguments. It takes as many combinations of <Expression -> Statement>s as you need. Each expression is evaluated. If the expression evaluates to true, the statement that follows it executes.

Although you can spend a great deal of time tuning a conditional expression such as one involving a Switch() function, it is still possible that none of the expressions evaluates to true. In this case, you can add a last expression as True and provide a subsequent statement to use. The syntax you would use is:

Switch(Expression1, What To Do If Expression1 Is True,
       Expression2, What To Do If Expression2 Is True,
       Expression_n, What To Do If Expression_n Is True,
       True, What To Do With A False Expression) As Some Value

Value Checking Functions

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 (on 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 form, click Parts Used
2. Double-click the first text box under the Sub Total label
3. In the Property Sheet, click the Data tab.
   Click Control Source and type =Nz([UnitPrice1]) * Nz([Quantity1])
4. On the form, click the second text box under Sub-Total.
   In the Property Sheet, click Control Source and type
   =Nz([UnitPrice2]) * Nz([Quantity2])
5. On the form click the third text box under Sub-Total.
   In the Property Sheet, click Control Source and type
   =Nz([UnitPrice3]) * Nz([Quantity3])
6. On the form, click the fourth text box under Sub-Total.
   In the Property Sheet, click Control Source and type
   =Nz([UnitPrice4]) * Nz([Quantity4])
7. On the form click the fifth text box under Sub-Total.
   In the Property Sheet, click Control Source and type
   =Nz([UnitPrice5]) * Nz([Quantity5])
8. Click the Order Summary tab
9. Click the Tax Amount text box
10. In the Property Sheet, click Control Source and change it as follows:
     
    =Nz([SubTotal1])+Nz([SubTotal2])+Nz([SubTotal3])+Nz([SubTotal4])+Nz([SubTotal5])
11. On the form, click the Total Labor text box
12. In the Data tab of the Property Sheet, change the expression in the Control Source as follows:
     
    =Nz([JobPrice1])+Nz([JobPrice2])+Nz([JobPrice3])+Nz([JobPrice4])+Nz([JobPrice5])
13. On the form, click the Tax Amount text box
14. In the Data tab of the Property Sheet, change the expression in the Control Source as follows:
    =CLng(CCur(Nz([txtTotalParts])+Nz([txtTotalLabor]))*CDbl(Nz([TaxRate]))*100)/100
15. On the form, click the unbound Total Order text box
16. In the Property Sheet, click Control Source and type:
    =CCur(Nz([txtTotalParts])) +CCur( Nz([txtTotalLabor])) + CCur(Nz([txtTaxAmount]))
17. Click Format and select Currency
     
    
18. Save the form
19. To preview the form, right-click the RepairOrders title bar and click Form View
20. To test the form, create the following record:
       
    
     
    
     
    
     
    
21. Close the RepairsOrders form

The conversion functions we reviewed are effective if they are supplied a value to convert. In some expressions, you will provide a data field as argument to use by a function. Even if you decide to convert a field to the appropriate value, you may need to first check whether the field is empty. This can be taken care of by the IsEmpty() function. 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.

Another 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 and the field displays Paul. If the user comes to that record, the field is not empty, it already contains a name, which in this case is Paul. If the user clicks in the field and deletes Paul, the field becomes empty. It is not null
• Imagine a field is used for a first name. If the user comes to a new record, the field for the first name may be empty (if you did not give it a default value). In this case, the field is Null: it is not empty because it has never contained anything. If the user types a name, and then deletes it, the field is not considered Null anymore: it has become empty

To check whether a field (or a value) is null, you can call the IsNull() function. Its syntax is:

IsNull(Value)

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 ever contained a value). If the field it null, this function returns True. If the field is not null, this function returns False.

When the word "operation" is stated, most people think of a numeric value, an operator, and another numeric value. Such values must be valid in order for the operation to succeed. It certainly would not make sense to multiply a Date Hired by an Hourly Salary. Therefore, before involving a value or field to an operation, you should check that it contains a valid number. To perform this checking, Microsoft Access provides the IsNumeric() function. Its syntax is:

IsNumeric(Value)

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

Logical Comparisons

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 renders a False value

An example of a comparison would be:

txtLength = txtWidth

In this case, the database engine would compare the value of a control named txtLength to the value of a control named txtWidth. If both controls hold the same value, the comparison would produce a True result. If the controls hold different values, the comparison would produce a False result.

To negate the equality comparison, you can use the NOT operator followed by paretheses. In the parentheses, type the equality expression. Here is an example:

NOT(txtLength = txtWidth)

This time, the database engine would compare the values of two controls named txtLength and txtWidth respectively. If both controls hold the same value, the comparison would produce a False result. If the controls hold different values, the comparison would produce a True result.

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

Value1 <> Value2

This comparison is performed between Value1 and Value2. If they hold different values, then the comparison produces a True value. If they hold the same value, the comparison produces False:

This shows you that the equality (=) and the inequality (<>) operators are opposite each other. Here is an example:

txtLength <> txtWidth

When the database engine performs this comparison, it checks the values held by both controls.

To negate this comparison, you can use the NOT operator and the comparison would be performed as if it were the equality comparison. Here is an example:

NOT(txtLength <> txtWidth)

When performing this comparison, if the controls hold the same value, the comparison would produce a True result. If they hold different values, the comparison would produce a False result.

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:

To negate this comparison, you can precede it with the NOT operator.

When comparing two values, you may want to know whether two fields hold the same value or if one is lower than the other. This comparison can be performed with 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 renders a False result:

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

To negate this comparison, you can precede it with the NOT operator.

Greater Than >

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

Value1 > Value2

The operation is performed on the values of Value1 and Value2. If Value1 holds a value greater than that of Value2, then the comparison produces True. Otherwise, the comparison produces False. That is, if the value of Value2 is greater than or equal to that of Value1, then the comparison produces False:

To negate this comparison, you can use the NOT operator. The comparison would be the same as if you were using the <= operator on two values.

Greater Than Or Equal >=

If you have two values and want to find out whether they hold similar values or the first is greater than the second, you can use the >= operator whose syntax is:

Value1 >= Value2

If both Value1 and Value2 hold the same value, then the comparison produces a True result. Similarly, if the left operand, Value1, holds a value greater than that of the right operand, Value2, the comparison still produces True. If the value of Value1 is less than the value of Value2, the comparison produces a False result:

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.

Lesson Summary

Exercises

Yugo National Bank

1. Open the Yugo National Bank1 database
2. Open the Employees table in Design View. Just under Title, add a new Yes/No field named CanCreateNewAccount
3. Open the Employees form in Design View and add the new CanCreateNewAccount field as follows:
    
   
4. Adjust the tab order so that the sequence would conform to the order of controls on the form
5. Save the form and close it
6. Open the Customers form, click the calendar in the Date Created control, and specify the following date values:
    

   Account Number	Date Created		Account Number	Date Created
   28-3782-80	02/04/2008		92-3782-42	02/04/2008
   38-4227-59	02/04/2008		68-6434-15	02/04/2008
   83-4654-73	02/04/2008		47-4783-29	02/05/2008
   82-3763-62	02/05/2008		72-3474-26	02/05/2008
   34-5458-04	02/05/2008		29-4586-46	02/05/2008
   68-3465-84	02/05/2008		40-4658-63	02/05/2008
   56-8468-25	02/05/2008		94-7785-03	02/08/2008
   37-5764-28	02/08/2008		34-9754-57	02/08/2008
   72-9375-44	02/08/2008		37-5490-26	02/08/2008
   20-3454-49	02/08/2008		76-5475-24	02/08/2008
   27-3457-84	02/08/2008

   Close the form

Watts A loan

1. Open the Watts A Loan1 database
2. Create a form for the Employees table. Save the form as Employees and design it to look like the other forms:

US Senate

1. Open the US Senate1 database 
2. Create a form for the Senators table. Save the form as Senator