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.

1. Start Microsoft Access
2. Open the WattsALoan1 database from the previous lesson

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", AcControlType.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

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.

To specify the value of a Boolean field 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

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.

The SQL supports all types of natural numbers through various data types. If you want the field to hold small positive numbers that can range from 0 to 255, apply a data type named BYTE or INTEGER1 (remember that SQL is not case-sensitive). Here are examples

Private Sub cmdCreateTable_Click()
    DoCmd.RunSQL "CREATE TABLE LibraryMembers(MemberName Text, " & _
                                             "MembershipStatus Byte, " & _
                                             "MembershipType Integer1);"
End Sub

When performing data entry on fields of integral types, assign a number in the natural range. If the field holds a byte-based number, assign a positive number between 0 and 255.

To create a field that can handle short natural numbers between -32,768 and 32,767, apply the SHORT, SMALLINT, or the INTEGER2 data type. Here are examples:

Private Sub cmdCreateTable_Click()
    DoCmd.RunSQL "CREATE TABLE ClassAttendance(Course Text, " & _
                                             "Registered Short, " & _
                                             "RoomCapacity Integer2);"
End Sub

If you are creating a field to use very large natural numbers between -2,147,483,648 and 2,147,483,647, specify its data type as INT, LONG, INTEGER, or INTEGER4. Here are examples:

Private Sub cmdCreateTable_Click()
    DoCmd.RunSQL "CREATE TABLE StateCensus(State Text, " & _
                                           "Men int, " & _
                                           "Women long, " & _
                                           "Children integer4);"
End Sub

If you apply one of the integral data types we saw above, when performing data entry, the user would have to provide a value for the field. Instead of letting the user specify the value, you can let Microsoft Access set the value for the field. To support this, Microsoft Access SQL supports two data types named COUNTER and AUTOINCREMENT. Here is an example of applying the COUNTER data type to a field:

Private Sub cmdTable_Click()
    DoCmd.RunSQL "CREATE TABLE Contractors(" & _
                 "ContractorNo COUNTER, " & _
                 "FullName TEXT NOT NULL);"
End Sub

There are various rules you must follow when using COUNTER or AUTOINCREMENT. One of the rules is that only one column of a table can have one of these data types.

By default, when you apply the COUNTER or the AUTOINCREMENT data type, when the user creates the first record, the field under the auto-incrementing value receives a number of 1. If the user creates a second record, the auto-incrementing value receives a number of 1, and so on. If you want, you can make the first record receive a number other than 1. You can also make it increment to a value other than 1. To apply this feature, the COUNTER and the AUTOINCREMENT types use a seed in their parentheses: COUNTER(x,y) or AUTOINCREMENT(x,y). The x value represents the starting value of the records. The y value specifies how much would be added to a value to get the next. Here is an example:

Private Sub cmdTable_Click()
    DoCmd.RunSQL "CREATE TABLE Contractors(" & _
                 "ContractorNo AUTOINCREMENT(5, 10), " & _
                 "FullName TEXT NOT NULL);"
End Sub

1. On the Ribbon, click Create
2. In the Queries section, click Query Design
3. On the Show Table dialog box, click Close
4. Right-click the middle of the window and click SQL View
5. Delete the default text in the window
6. Type the following:

   CREATE TABLE LoanTypes
   (
       LoanTypeID AUTOINCREMENT(1, 1) NOT NULL,
       LoanType varchar(50),
       Description memo,
       CONSTRAINT PK_LoanTypes PRIMARY KEY(LoanTypeID)
   );

7. To execute the statement, in the Results section of the Ribbon, click the Run button
8. Delete the whole code in the Query2 window
9. To start another table, type the following:

   CREATE TABLE LoansAllocations
   (
       LoanNumber COUNTER(100001, 1) NOT NULL,
       Constraint PK_LoansAllocations Primary Key(LoanNumber)
   );

10. To execute the statement, in the Results section of the Ribbon, click the Run button
11. Close the Query2 window
12. When asked whether you want to save, click No
13. On the Ribbon, click Create
14. In the Forms section, click Form Design
15. Using the Properties window, set the Record Source field to LoanTypes
16. Design the form as follows:
     
    
17. Close the form
18. When asked whether you want to save it, click Yes
19. In the Navigation pane, double-click the LoanTypes form
20. Create the loan types
21. Close the form

As seen for variables, a field can use a decimal value with single or double-precision. If you are creating the field in SQL, specify the data type of the column as NUMBER, NUMERIC, DOUBLE, FLOAT8, or IEEEDOUBLE.

1. On the Ribbon, click Create
2. In the Queries section, click Query Design
3. On the Show Table dialog box, click Close
4. Right-click the middle of the window and click SQL View
5. Delete the default text in the window
6. Complete the code in the Query1 window as follows:

   ALTER TABLE Employees
   ADD COLUMN HourlySalary Double;

7. To execute the statement, in the Results section of the Ribbon, click the Run botton
8. Close the Query1 window
9. When asked whether you want to save, click No
10. On the Ribbon, click Create
11. In the Forms section, click Form Design
12. Using the Properties window, set the Record Source field to Employees
13. Design the form as follows:
     
    
14. On the form, click the FirstName text box
15. In the Properties window, click Event and double-click On Lost Focus
16. Click the ellipsis button to launch Microsoft Visual Basic
17. Implement the event as follows:

    Private Sub FirstName_LostFocus()
        If FirstName = "" Then
            If LastName = "" Then
                FullName = ""
            Else
                FullName = LastName
            End If
        Else
            FullName = LastName & ", " & FirstName
        End If
    End Sub

18. In the Objects combo box, select LastName
19. In the Procedure combo box, select Lost Focus
20. Implement the event as follows:

    Private Sub LastName_LostFocus()
        If LastName = "" Then
            If LastName = "" Then
                FullName = ""
            Else
                FullName = LastName
            End If
        Else
            FullName = LastName & ", " & FirstName
        End If
    End Sub

21. Close the form
22. When asked whether you want to save it, click Yes
23. In the Navigation Pane, double-click the Employees form
24. Create the employees
25. Close the form

If you want a field that uses monetary values and you are using SQL, specify the data type as Money or Currency. Here is an example:

Private Sub cmdTable_Click()
    DoCmd.RunSQL "CREATE TABLE Employees(" & _
                 "FullName Text, " & _
                 "WeeklyHours Double, " & _
                 "HourlySalary Money);"
End Sub

Both Money and Currency have the same effect in Microsoft Access.

Data entry on a currency-based field follows the same rules as for natural numbers and decimal number fields. While the user can enter the currency symbol in a field ($ for US English), you cannot. If you are programmatically performing data entry on a currency-based field, assign only the value made of digits and possibly a decimal separator.

1. In the Navigation pane, right-click LoansAllocations and click Design View
2. Change the design of the table as follows:
    

   Field Name	Data Type	Field Size	Format	Caption
   LoanNumber				Loan Number
   EmployeeNumber	Text	10		Employee Number
   LoanTypeID	Number	Long Integer		Loan Type
   LoanAmount	Number	Double	Fixed	Loan Amount
   InterestRate	Number	Double	Percent	Interest Rate
   Periods	Number	Double	Fixed
   InterestAmount	Number	Double	Fixzed	Interest Amount
   FutureValue	Number	Double	Fixed	Future Value
   MonthlyPayment	Number	Double	Fixed	Monthly Payment
   Notes	Memo

3. Close the table
4. When asked whether you want to save, click Yes
5. On the Ribbon, click Create
6. In the Forms section, click Form Design
7. Double-click the button at the intersection of the rulers.
   In the Properties window, click All and set the Record Source field to LoansAllocations
8. In the Controls section of the Ribbon, click the Text Box and click the form
9. Delete its accompanying label
10. Using the Properties window, change its Name to txtEmployeeName
11. From the Controls section of the Ribbon, click the Combo Box and click the form
12. In the first page of the wizard, make sure the first radio is selected and click Next
13. In the second page of the wizard, click Table LoanTypes
14. Click Next
15. In the Available Fields of the third page of the wizard, double-click LoanType
16. In the fourth page of the wizard, click Next
17. In the fifth page of the wizard, click Next
18. In the sixth page of the wizard, click Next
19. In the seventh page of the wizard, click the arrow of the combo box and select LoanTypeID
20. Click Next
21. Click Finish
22. Complete the design of the form as follows:
     
    

In our introduction to procedures, we saw different ways of creating procedures and functions. Before creating a new function that perform a specific task, first find out if that function exists already. The library built in Microsoft Access is very large and provides functions that cover many subjects, including general topics, algebra, conversion, finance, accounting, date, time, and strings, etc. The available functions are highly reliable so you can safely use them.

One of the most valuable operations you will perform on a value consists of finding out whether it is numeric or not. To assist you with this, the Visual Basic language provides a function named IsNumeric. Its syntax is:

Public Function IsNumeric(ByVal Expression As Variant) As Boolean

This function takes as argument the value or expression to be evaluated. If the argument holds or can produce a valid integer or a decimal value, the function returns True. Here is an example:

Private Sub cmdFunction_Click()
    Dim Value As Variant

    Value = 258.08 * 9920.3479

    msgbox "Is Numeric? " & IsNumeric(Value)
End Sub 

If the argument is holding any other value that cannot be identified as a number, the function produces False. Here is an example:

Private Sub cmdFunction_Click()
    Dim Value As Variant

    Value = #12/4/1770#

    MsgBox "Is Numeric? " & IsNumeric(Value)
End Sub

This would produce:

The absolute value of a number x is x if the number is (already) positive. If the number is negative, then its absolute value is its positive equivalent. For example, the absolute value of 12 is 12, while the absolute value of -12 is 12.

To let you find the absolute value of a number, the Visual Basic language provides a function named Abs. Its syntax is:

Function Abs(number) As Number

This function takes one argument. The argument must be a number or an expression convertible to a number:

• If the argument is a positive number, the function returns it
• If the argument is zero, the function returns 0
• If the argument is a negative number, the function is returns its equivalent positive value

Here is an example that retrieves the number in a text box named txtNumber, finds the absolute value of that number, and displays it in the same text box:

Private Sub cmdAbsoluteValue_Click()
    txtNumber = Abs(txtNumber)
End Sub