Practical
Learning: Introducing Boolean Types
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.
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):
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.
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.
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.
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.
|
Database Conditional Values
Subscribe to:
Post Comments (Atom)
Related Scripting
No comments:
Post a Comment