Data Entry and Logical Values

After doing this, the cell under the column header would become equipped with a check box:

Alternatively, you can use a built-in Boolean-based field to create the new column. To start, display the Field Templates window by clicking New Field from the Ribbon. In the Field Templates, under the Basic Fields category, drag Checkbox and drop it on the table.

To create a Boolean field in the Design View of a table, after specifying the name of the column, set its Data Type to Yes/No. Here is an example:

A Text data type allows the user to type any kind of characters or group of characters.

In the Design View of a table, after specifying the data type of a column as Yes/No, in the lower section of the window, click General and specify how the control's value would be set. You have an option among a True/False, Yes/No, and On/Off:

After setting the format, you can click the Lookup tab to specify how the field would display its value. The options are a check box, a combo box, or a text box:

You can create a Boolean field on a form. One option is to create a field that is linked to a column of a table. The easiest way to do that, after displaying the form in Design View and clicking the Add Existing Fields from the Ribbon, from the Field List, drag the Boolean-based column and drop it on the form.

Whether linking it to a column of a table or not, you can add a check box to a form. To do that, after displaying the form in Design View, in the Controls section of the Ribbon, click the Check Box and click the form. You can then use the control as a normal Microsoft Windows object. If you want to link it to a column of a table, in its Properties window, set its Control Source to that column. You can also add a combo box to a form and set its Record Source property to a Boolean-based field.

To programmatically create a check box, call the CreateControl() method and pass the second argument as acCheckBox. Here is an example:

Private Sub cmdCreateControl_Click()
    Dim ctlIsMarried As Control
    
    Set ctlIsMarried = CreateControl("Exercise", acCheckBox)

    Set ctlIsMarried = Nothing
End Sub

If you want the check box to be linked to a column of a table, pass the name of the table as the fourth argument and the name of the column as the fifth argument. Here is an example:

Private Sub cmdCreateControl_Click()
    Dim ctlIsMarried As Control
    
    Set ctlIsMarried = CreateControl("Fundamentals", _
                                    AcControlType.acCheckBox, _
                                    acSection.acDetail, _
                                    "[Student Registration]", _
                                    "[Full Time Student]", _
                                    840, 300)

    Set ctlIsMarried = Nothing
End Sub

If you are programmatically creating the column on a table, using the Microsoft Access Object Library, and the column would be Boolean-based, set its data type as dbBoolean. Here is an example:

Private Sub cmdTableCreator_Click()
    Dim curDatabase As Object
    Dim tblEmployees As Object
    Dim colFullName As Object
    Dim colIsMarried As Object

    Set curDatabase = CurrentDb
    Set tblEmployees = curDatabase.CreateTableDef("Employees")
    
    Set colFullName = tblEmployees.CreateField("FullName", dbText)
    tblEmployees.Fields.Append colFullName
    Set colIsMarried = tblEmployees.CreateField("IsMarried", dbBoolean)
    tblEmployees.Fields.Append colIsMarried
    
    ' Add the Students table to the current database
    curDatabase.TableDefs.Append tblEmployees
End Sub

You can also use the DB_BOOLEAN type.

If you are creating a table using DAO and you want a column to hold Boolean values, specify its data type as DB_BOOLEAN. Here is an example:

Private Sub cmdTableCreation_Click()
    Dim curDatabase As DAO.Database
    Dim tblStudents As DAO.TableDef
    Dim colFullName As DAO.Field
    Dim colWasTransfered As DAO.Field

    Set curDatabase = CurrentDb
    
    Set tblStudents = curDatabase.CreateTableDef("Students")
    
    Set colFullName = tblStudents.CreateField("FullName", dbText)
    tblStudents.Fields.Append colFullName
    
    Set colWasTransfered = tblStudents.CreateField("WasTransfered", DB_BOOLEAN)
    tblStudents.Fields.Append colWasTransfered
    
    curDatabase.TableDefs.Append tblStudents
End Sub

If you are using SQL to create the column, set its data type as YESNO, BIT, or LOGICAL. Here are examples:

Private Sub cmdTable_Click()
    DoCmd.RunSQL "CREATE TABLE Contractors(" & _
                 "FullName TEXT, " & _
                 "AvailableOnWeekend BIT, " & _
                 "OwnsACar LOGICAL, " & _
                 "CanShareOwnCar YESNO);"
End Sub

These are equivalent to Microsoft Access' Yes/No and to Microsoft Visual Basic's Boolean data type.

After creating either a Boolean field or a check box, you (and the user) can use it as you see fit. Normally, most users know how to use a combo box. You also also will usually need a way to programmatically use a check box.

To programmatically specify the value of a check box, access its Value property and assign True or False to it. Here is an example:

Private Sub cmdIsMarried_Click()
    chkIsMarried.Value = True
End Sub

If you set the value to True, the control would display a check mark. If you set it to False, the check box would be emptied.

Remember that the Field class of the Microsoft Access Object Library is equipped with a property named Value. To specify the value of a Boolean field, assign True or False to it. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim curDatabase As Object
    Dim tblEmployees As Object
    Dim colFullName As Object
    Dim colIsMarried As Object

    Set curDatabase = CurrentDb
    Set tblEmployees = curDatabase.CreateTableDef("Employees")
    
    Set colFullName = tblEmployees.CreateField("FullName", dbText)
    tblEmployees.Fields.Append colFullName
    Set colIsMarried = tblEmployees.CreateField("IsMarried", dbBoolean)
    tblEmployees.Fields.Append colIsMarried
    
    ' Add the Students table to the current database
    curDatabase.TableDefs.Append tblEmployees
End Sub

Private Sub cmdCreateRecord_Click()
    Dim curDatabase As Object
    Dim rstStudents As Object
    
    Set curDatabase = CurrentDb
    Set rstStudents = curDatabase.OpenRecordset("Employees")
    
    rstStudents.AddNew
    rstStudents("FullName").Value = "Patricia Katts"
    rstStudents("IsMarried").Value = True
    rstStudents.Update
    
    Set rstStudents = Nothing
    Set curDatabase = Nothing
End Sub

Also, remember that, in the Visual Basic language, the compiler can consider numeric values for Boolean fields. This means that you can assign True or False to the Value property or a Boolean field. Here is an example:

Private Sub cmdCreateRecord_Click()
    Dim curDatabase As Object
    Dim rstStudents As Object
    
    Set curDatabase = CurrentDb
    Set rstStudents = curDatabase.OpenRecordset("Employees")
    
    rstStudents.AddNew
    rstStudents("FullName").Value = "Patricia Katts"
    rstStudents("IsMarried").Value = 78345
    rstStudents.Update
    
    Set rstStudents = Nothing
    Set curDatabase = Nothing
End Sub

If you assign 0, the field would receive a value of False, equivalent to an empty check box. If you assign any other value, the field would receive a value of True, which is the same as the check box being checked.

Once again, keep in mind that the Microsoft DAO Library uses the same techniques or formulas to perform similar operations. In the DAO library, the DAO.Field class provides the Value property. To set the Boolean value of a DAO.Field object, assign True or False to its Value . Here is an example:

Private Sub cmdCreateRecord_Click()
    Dim dbExercise As DAO.Database
    Dim rstEmployees As DAO.Recordset
    
    Set dbExercise = CurrentDb
    Set rstEmployees = dbExercise.OpenRecordset("Employees")
    
    rstEmployees.AddNew
    rstEmployees("FullName").Value = "Peter Sandrich"
    rstEmployees("IsMarried").Value = False
    rstEmployees.Update
    
    Set rstEmployees = Nothing
    Set dbExercise = Nothing
End Sub

As mentioned for the Microsoft Access Object library, you can assign a number to the Value property of a DAO.Field object.

We saw that, in SQL, to create a Boolean field, you use the BIT, the YESNO, or the LOGICAL data type. Here the example we saw:

Private Sub cmdCreateTable_Click()
    DoCmd.RunSQL "CREATE TABLE Contractors(" & _
                 "FullName TEXT, " & _
                 "AvailableOnWeekend Bit, " & _
                 "OwnsACar Logical, " & _
                 "CanShareOwnCar YesNo);"
End Sub

To specify its value during data entry, set its value to 0 or 1. Here are examples:

Private Sub cmdCreateRecord_Click()
    DoCmd.RunSQL "INSERT INTO Contractors VALUES(" & _
                 "'Arlen Sinoko', 1, 0, 1);"
End Sub

If you set the value to 0, the field receives a value of false, which is the same as the check box being empty. If you set the value to 1, the field is considered true. In your code, you can also specify the value as True or False. Here are examples:

Private Sub cmdCreateRecord_Click()
    DoCmd.RunSQL "INSERT INTO Contractors VALUES(" & _
                 "'William Woods', False, False, True);"
End Sub

As mentioned already, value is referred to as Boolean if it can be either true or false. As you may imagine, the essence of a Boolean value is to check that a condition is true or false, valid or invalid. In your code, you can stored a Boolean value in a variable. To declare such a variable, use the Boolean keyword. Here is an example:

Sub cmdBooleanVariable_Click()
    Dim EmployeeIsMarried As Boolean
End Sub

To actually use a Boolean variable, you can assign a value to it. To initialize a Boolean variable, assign it a True or a False value. By default, if you declare a Boolean variable but do not initialized it, it receives a value of False. Here is an example that checks it:

Private Sub cmdBooleanVariable_Click()
    Dim isBoolean As Boolean
    
    MsgBox isBoolean
End Sub

A Boolean variable can also deal with numeric values. The False value is equivalent to 0. For example, instead of False, you can initialize a Boolean variable with 0. Any other numeric value, whether positive or negative, corresponds to True:

Private Sub cmdBooleanVariable_Click()
    Dim isBoolean As Boolean
    
    isBoolean = -792730
    MsgBox isBoolean
End Sub

The number can be decimal or hexadecimal:

Sub cmdBooleanVariable_Click()
    Dim EmployeeIsMarried As Boolean

    EmployeeIsMarried = &HFA26B5
    
End Sub

Boolean values can be involved with procedures. This means that a Boolean variable can be passed to a procedure and/or a function can be made to return a Boolean value. Some of the issues involved with procedures require conditional statements that we will study in the next lessons. Still, the basic functionality is possible with what we have learned so far.

To pass an argument as a Boolean value, in the parentheses of the procedure, type the name of the argument followed by the As Boolean expression. Here is an example:

Private Sub CheckingEmployee(ByVal IsFullTime As Boolean)

End Sub

In the same way, you can pass as many Boolean arguments as you need, and you can combine Boolean and non-Boolean arguments as you judge necessary. Then, in the body of the procedure, use (or do not use) the Boolean argument as you wish.

You can create a function that returns a Boolean value. When declaring the function, specify its name and the As Boolean expression on the right side of the parentheses. Here is an example:

Public Function IsDifferent() As Boolean

Of course, the function can take arguments of any kind you judge necessary:

Public Function IsDifferent(ByVal Value1 As Integer, _
                            ByVal Value2 As Integer) As Boolean

In the body of the function, do whatever you judge necessary. Before exiting the function, you must return a value that evaluates to True or False.

To assist you with validating some values or variables to true or false, the Visual Basic language provides many functions. First, to convert a value to Boolean, you can use the CBool() function. Its syntax is:

Function CBool(ByVal Expression As Variant) As Boolean

Like all conversion functions, CBool takes one argument, the expression to be evaluated. It should produce a valid Boolean value. If it does, the function returns True or False.

After declaring a variable, memory is reserved for but you should assign value to it before using it. At any time, to check whether a variable has been initialized, you can call the IsEmpty() function. Its syntax is:

Public Function IsEmpty(ByVal Expression As Variant) As Boolean

When calling this function, pass the name of a variable to it. If the variable was already initialized, the function would return True. Otherwise, it would return False.

In previous lessons, we saw how to declare variables of different types, including Object. At any time, to find out whether a variable in an Object type, you can call the IsObject() function. Its syntax is:

Public Function IsObject(ByVal VariableName As String) As Boolean

This function takes as argument the name of a variable. If the argument represents an Object type, the function returns True. Otherwise, it returns False.

After declaring a variable, you should initialize it with a valid value. Sometimes you will not. In some other cases, you may be using a variable without knowing with certainty whether it is holding a valid value. To assist you with checking whether a variable is currently holding a valid value, you can call the IsNull() function. Its syntax is:

Public Function IsNull(ByVal Expression As Variant) As Boolean

When calling this function, pass the name of a variable to it. If the variable is currently holding a valid value, this function would returns True. Otherwise, it would return False.

Microsoft Access doesn't use conditionals statements like traditional computer languages do. It relies on special condition-oriented functions to perform the same operations. One of these functions is called Nz.

The Nz() function is used to check the value of an expression or a control. Its syntax is:

Nz(Value, ByVal Optional ValueIfNull IS NULL) As Variant

The function checks the value of the (first) argument. If Value is null, the function returns 0 or an empty string. The second argument is optional. You can provide it as an alternative to 0 in case the Value argument is null. This means, that, when the first argument is null, instead of returning 0 or an empty string, the Nz() function would return to the value of the second argument.

A comparison is an operation used to get the boolean result of two values one checked against the other. Such a comparison is performed between two values of the same type.

To compare two variables for equality, use the = operator. Its syntax is:

Value1 = Value2

The equality operation is used to find out whether two variables (or one variable and a constant) hold the same value. From our syntax, the value of Value1 would be compared with the value of Value2. If Value1 and Value2 hold the same value, the comparison produces a True result. If they are different, the comparison renders false or 0.

Here is an example:

Private Sub cmdBooleanVariable_Click()
    Dim IsFullTime As Boolean

    MsgBox "Is Employee Full Time? " & IsFullTime

    IsFullTime = True
    MsgBox "Is Employee Full Time? " & IsFullTime
End Sub

This would produce:

As opposed to checking for equality, you may instead want to know whether two values are different. The operator used to perform this comparison is <> and its formula is:

Variable1 <> Variable2

If the operands on both sides of the operator are the same, the comparison renders false. If both operands hold different values, then the comparison produces a true result. This also shows that the equality = and the inequality <> operators are opposite.

Here is an example:

Public Function IsDifferent(ByVal Value1 As Integer, _
                            ByVal Value2 As Integer) As Boolean
    IsDifferent = Value1 <> Value2
End Function

Private Sub cmdBooleanVariable_Click()
    Dim a%, b%
    Dim Result As Boolean

    a% = 12: b% = 48
    Result = IsDifferent(a%, b%)

    MsgBox "The resulting comparison of 12 <> 48 is " & Result
End Sub

This would produce:

To find out whether one value is lower than another, use the < operator. Its syntax is:

Value1 < Value2

The value held by Value1 is compared to that of Value2. As it would be done with other operations, the comparison can be made between two variables, as in Variable1 < Variable2. If the value held by Variable1 is lower than that of Variable2, the comparison produces a True.

Here is an example:

Private Sub cmdBooleanVariable_Click()
    Dim PartTimeSalary, ContractorSalary As Double
    Dim IsLower As Boolean
       
    PartTimeSalary = 20.15
    ContractorSalary = 22.48
    IsLower = PartTimeSalary < ContractorSalary

    MsgBox ("Part Time Salary:  " & PartTimeSalary & vbCrLf & _
            "Contractor Salary: " & ContractorSalary & vbCrLf & _
            "Is PartTimeSalary < ContractorSalary? " & IsLower)

    PartTimeSalary = 25.55
    ContractorSalary = 12.68
    IsLower = PartTimeSalary < ContractorSalary

    MsgBox ("Part Time Salary:  " & PartTimeSalary & vbCrLf & _
            "Contractor Salary: " & ContractorSalary & vbCrLf & _
            "Is PartTimeSalary < ContractorSalary? " & IsLower)
End Sub

This would produce:

The previous two operations can be combined to compare two values. This allows you to know if two values are the same or if the first is less than the second. The operator used is <= and its syntax is:

Value1 <= Value2

The <= operation performs a comparison as any of the last two. If both Value1 and VBalue2 hold the same value, result is true or positive. If the left operand, in this case Value1, holds a value lower than the second operand, in this case Value2, the result is still true:

When two values of the same type are distinct, one of them is usually higher than the other. VBasic provides a logical operator that allows you to find out if one of two values is greater than the other. The operator used for this operation uses the > symbol. Its syntax is:

Value1 > Value2

Both operands, in this case Value1 and Value2, can be variables or the left operand can be a variable while the right operand is a constant. If the value on the left of the > operator is greater than the value on the right side or a constant, the comparison produces a True value. Otherwise, the comparison renders False or null:

Here is an example:

Private Sub cmdBooleanVariable_Click()
    Dim PartTimeSalary, ContractorSalary As Double
    Dim IsLower As Boolean

    PartTimeSalary = 20.15
    ContractorSalary = 22.48
    IsLower = PartTimeSalary > ContractorSalary

    MsgBox ("Part Time Salary:  " & PartTimeSalary & vbCrLf & _
            "Contractor Salary: " & ContractorSalary & vbCrLf & _
            "Is PartTimeSalary > ContractorSalary? " & IsLower)

    PartTimeSalary = 25.55
    ContractorSalary = 12.68
    IsLower = PartTimeSalary > ContractorSalary

    MsgBox ("Part Time Salary:  " & PartTimeSalary & vbCrLf & _
            "Contractor Salary: " & ContractorSalary & vbCrLf & _
            "Is PartTimeSalary > ContractorSalary? " & IsLower)
End Sub

This would produce:

The greater than or the equality operators can be combined to produce an operator as follows: >=. This is the "greater than or equal to" operator. Its syntax is:

Value1 >= Value2

A comparison is performed on both operands: Value1 and Value2. If the value of Value1 and that of Value2 are the same, the comparison produces a True value. If the value of the left operand is greater than that of the right operand, the comparison still produces True. If the value of the left operand is strictly less than the value of the right operand, the comparison produces a False result:

Here is a summary table of the logical operators we have studied: