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:
Practical Learning: Introducing Boolean Types
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:
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:
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
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.
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.
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.
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.
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.
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:
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
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 (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
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.
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):
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.
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