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

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

Save the table

To change its view, in the lower-right section, click the
Datasheet View button

Boolean Fields

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

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

On the table, double-click the newly added Checkbox column header
and type Has Finished Basement?

Right-click the Properties tab and click Design View

In the top section of the table, right-click Year Built and click
Insert Rows

Type With Indoor Garage

Press Tab and type y

Save and close the table

Options of Boolean Fields

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

In the top section of the table, click Has a Finished Basement?

In the lower section of the table, click Lookup

Click Display Control, then click the arrow of its combo box and
select Combo Box

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.

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.

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

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

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

Practical
Learning: Introducing Logical Functions

Open the College Park Auto Repair1 database you started in
Lesson 15 and
continued in Lesson 20

In the Navigation Pane, under Tables, double-click RepairOrders to
open it in the Datasheet View

On the table, click the cell under Customer Name

On the Ribbon, click Fields

In the Field Validation section, click Validation and click Record
Validation Rule

In the Expression Elements list, make sure RepairOrders is
selected.In the Expression Categories list, double-click
CustomerName

Type IS NOT NULL

Click OK

In the Field Validation section, click Validation and click Record
Validation Message

Type Make sure you specify the name of the customer

Click OK

Close the table

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

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:

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

In the Navigation Pane, under Forms, right-click RepairOrders and
click Design View

Click Jobs Performed

Click the JobPerformed1 text box

In the Property Sheet, click the Event tab

Click On Lost Focus and type

=IIf([JobPerformed1] Is
Null, MsgBox("Specify at least one of the jobs that were performed on
the car"),"")

Save the form

The Choose Function

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.

The Switch Function

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

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 (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

On the form, click Parts Used

Double-click the first text box under the Sub Total label

In the Property Sheet, click the Data tab.Click Control Source
and type =Nz([UnitPrice1]) * Nz([Quantity1])

On the form, click the second text box under Sub-Total.In the
Property Sheet, click Control Source and type=Nz([UnitPrice2]) *
Nz([Quantity2])

On the form click the third text box under Sub-Total.In the
Property Sheet, click Control Source and type=Nz([UnitPrice3]) *
Nz([Quantity3])

On the form, click the fourth text box under Sub-Total.In the
Property Sheet, click Control Source and type=Nz([UnitPrice4]) *
Nz([Quantity4])

On the form click the fifth text box under Sub-Total.In the
Property Sheet, click Control Source and type=Nz([UnitPrice5]) *
Nz([Quantity5])

To preview the form, right-click the RepairOrders title bar and
click Form View

To test the form, create the following record:

Close the RepairsOrders form

Field Emptiness

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.

Nullity Checking

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.

Numerical Checking

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 =

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.

Inequality <>

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.

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:

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

Less Than Or Equal <=

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

Open the Yugo National Bank1 database

Open the Employees table in Design View. Just under Title, add a new
Yes/No field named CanCreateNewAccount

Open the Employees form in Design View and add the new
CanCreateNewAccount field as follows:

Adjust the tab order so that the sequence would conform to the order
of controls on the form

Save the form and close it

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

Open the Watts A Loan1 database

Create a form for the Employees table. Save the form as Employees
and design it to look like the other forms: