Microsoft Access Database Development With VBA

Data Entry and Types of Fields

 

Boolean Values in Records

 

Introduction to Boolean Fields

A Boolean value is one that can have a True or a False value. In the same way, a field can hold a Boolean value. To create a Boolean-based field in SQL, set its data type to 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.

Practical LearningPractical Learning: Introducing NULL and NOT NULL Fields

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

A Check Box as a Boolean Field on a Form

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

Data Entry With a Boolean Field

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.

Boolean Data Entry With the SQL

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

Passing a Boolean Variable as Argument

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.

Boolean Values and Methods

   

Returning a Boolean Value

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.

Converting a Value to Boolean

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.

Checking Whether a Variable Has Been Initialized

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.

Checking Whether a Variable is an Object

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.

Checking for Nullity

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.

Checking for Non-Zero

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.

Integral Values

   

Tiny Integers

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.

Small or Short Integers

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

Long Integers

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

Automatic Integers

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

Practical LearningPractical Learning: Creating an Automatic Integer

  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 Run
  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 Run
  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:
     
    Watts A Loan - Loan Types - Form Design
  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

Decimal Values

   

Single-Precision Numeric Values

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.

Practical LearningPractical Learning: Creating a Decimal Value Field

  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 Run
  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:
     
    Watts A Loan - Employees - Form Design
  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 Ellipsis 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

Currency Values

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.

Practical LearningPractical Learning: Creating Decimal Value Fields

  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 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 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:
     
    Watts A Loan - Loans Allocations - Form Design

Built-In Numeric Functions

 

Introduction

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.

Checking Whether a Value is Numeric

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:

IsNumeric

The Absolute Value

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
 
 
 

Getting the Integral Part of a Number

If you have a decimal number but are interested only in the integral part, to assist you with retrieving that part, the Visual Basic language provides the Int() and the Fix() functions. Their syntaxes are:

Function Int(ByVal Number As { Number | Expression } ) As Integer
Function Fix(ByVal Number As { Number | Expression } ) As Integer

Each function must take one argument. The value of the argument must be number-based. This means it can be an integer or a floating-point number. If the value of the argument is integer-based, the function returns the (whole) number. Here is an example

Private Sub cmdFunction_Click()
    Dim Number As Integer

    Number = 28635
    MsgBox Int(Number)
End Sub

This would produce:

Int

If the value of the argument is a decimal number, the function returns only the integral part. Here is an example

Private Sub cmdFunction_Click()
    Dim Number As Double

    Number = 7942.225 * 202.46
    MsgBox Int(Number)
End Sub

This would produce:

Int

This function always returns the integral part only, even if you ask it to return a floating-point-based value. Here is an example:

Private Sub cmdFunction_Click()
    Dim Number As Single

    Number = 286345.9924
    MsgBox Int(Number)
End Sub

This would produce:

Int

Expression Evaluation

To assist you with evaluating an expression, the Visual Basic language provides the Eval() function. Its syntax is:

Eval(Expression)

The argument is passed to this function as a string. The argument can be as simple as an arithmetic operation as in 12 * 11, which would be Eval("12*11") or it can be a complex expression. When the function receives the argument, it uses its own built-in mechanism to analyze it and find out the type of the value it should return. If the expression appears as a calculation, then the function would return a numeric value. Otherwise, the function may return a string.

The Numeric Value of an Expression

When an expression is supposed to produce a numeric value, it is important to make sure you get that value before involving it in another operation. Microsoft Office provides a function that can be used to easily get the numeric value of an expression. The function is called Val and its syntax is:

Val(Expression)

In reality, this function can be considered as two in one. In other words, it can produce either a natural or a real number. This function takes as argument either an unknown value or an expression, such as an algebraic calculation. In most cases, or whenever possible, you should be able to predict the type of expression passed as argument. For example, if you pass an algebraic operation that calculates the sum of two natural numbers, you should be able to predict that the function would return a natural number. In this case, you can retrieve the integer that the function returns. Here is an example:

Private Sub cmdValue_Click()
    Dim intValue%
    
    intValue% = Val(145 + 608)
    txtValue = intValue%
End Sub

In the same way, you can ask this function to perform an algebraic operation on two or more natural numbers, two or more decimal numbers, two or more numbers that include at least one decimal number. If the function receives an operation that involves two natural numbers, it would return a natural number. If the function receives an operation that involves at least one decimal number and one or more natural numbers, the function would return a decimal number. If the function receives an operation that involves decimal numbers, it would produce a decimal number.

Regardless of the types of numbers that this function receives, you still can impose the type of value you want to retrieve. If the function receives an operation that involves only natural numbers, you may prefer to get a decimal number from it. If the function receives an operation that involves at least one decimal number and one or more natural numbers, you can still retrieve only the natural number. Here is an example:

Private Sub cmdValue_Click()
    Dim intValue%
    
    intValue% = Val(455 + 1250.85 + 88)
    txtValue = intValue%
End Sub

This call of the Val() function would return a decimal number but you mange to retrieve the natural number.

Number Formatting

 

Introduction

To display a value in a field, Microsoft Access primarily relies on the computer's Regional Options or Regional Settings that indicate how numbers should appear in an application. Numbers can be considered in various formats including accounting, scientific, fractions, and currency. Microsoft Access is configured to recognize and display numbers in any format of your choice. To do that, it uses some default settings. Still, if you want, you can control how a field should display its value and when.

Introduction to the Format() Number

The Visual Basic language provides a function named Format. This function can be used for different types of values The most basic technique consists of passing it an expression that holds the value to display. The syntax of this function is:

Function Format(ByVal Expression As Variant, _
   Optional ByVal Style As String = "" _
) As String

The first argument is the value that must be formatted. Here is an example:

Private Sub cmdFunction_Click()
    Dim Number As Double

    Number = 20502.48
    MsgBox Format(Number)
End Sub

This would produce:

Format

The second argument is optionally. It specifies the type of format you want to apply.

Formatting a Number

To programmatically control how the number should display, you can pass the second argument to the Format() function. To produce the number in a general format, you can pass the second argument as "g", "G", "f", or "F" .

To display the number with a decimal separator, pass the second argument as "n", "N", or "Standard". Here is an example:

Private Sub cmdFunction_Click()
    Dim Number As Double

    Number = 20502.48
    MsgBox Format(Number, "STANDARD")
End Sub

This would produce:

Format

An alternative to get this format is to call a function named FormatNumber. Its syntax is:

Function FormatNumber(
   ByVal Expression As Variant,
   Optional ByVal NumDigitsAfterDecimal As Integer = -1,
   Optional ByVal IncludeLeadingDigit As Integer,
   Optional ByVal UseParensForNegativeNumbers As Integer,
   Optional ByVal GroupDigits As Integer
) As String

Only the first argument is required and it represents the value to display. If you pass only this argument, you get the same format as the Format() function called with the Standard option. Here is an example:

Private Sub cmdFunction_Click()
    Dim Number As Double

    Number = 20502.48
    MsgBox FormatNumber(Number)
End Sub

This would produce the same result as above.

If you call the Format() function with the Standard option, it would consider only the number of digits on the right side of the decimal separator. If you want to display more digits than the number actually has, call the FormatNumber() function and pass a second argument with the desired number. Here is an example:

Private Sub cmdFunction_Click()
    Dim Number As Double

    Number = 20502.48
    MsgBox FormatNumber(Number, 4)
End Sub

This would produce:

Format

In the same way, if you want the number to display with less numbers on the right side of the decimal separator, specify that number.

You can call the Format() function to format the number with many more options. To represent the integral part of a number, you use the # sign. To specify the number of digits to display on the right side of the decimal separator, type a period on the right side of # followed by the number of 0s representing each decimal place. Here is an example:

Private Sub cmdFunction_Click()
    Dim Number As Double

    Number = 20502.48
    MsgBox Format(Number, "#.00000")
End Sub

This would produce:

Format

The five 0s on the right side of the period indicate that you want to display 5 digits on the right side of the period. You can enter as many # signs as you want; it would not change anything. Here is an example:

Private Sub cmdFunction_Click()
    Dim Number As Double

    Number = 20502.48
    MsgBox Format(Number, "##########.00000")
End Sub

This would produce the same result as above. To specify that you want to display the decimal separator, include its character between the # signs. Here is an example:

Private Sub cmdFunction_Click()
    Dim Number As Double

    Number = 20502.48
    MsgBox Format(Number, "###,#######.00000")
End Sub

This would produce:

Format

You can include any other character or symbol you want in the string to be part of the result, but you should include such a character only at the beginning or the end of the string, otherwise the interpreter might give you an unexpected result.

Practical LearningPractical Learning: Formatting a Number

  1. On the form, right-click the Evaluate Loan button and click Build Event...
  2. In the Choose Builder dialog box, click Code Builder and click OK
  3. Implement the event as follows:
    Private Sub cmdEvaluate_Click()
        If IsNull(LoanNumber) Then
            Exit Sub
        End If
        
        If IsNull(LoanAmount) Then
            MsgBox "You must specify the amount of the loan.", _
                   vbOKOnly Or vbInformation, _
                   "Whatts A Loan"
            Exit Sub
        End If
        
        If IsNull(InterestRate) Then
            MsgBox "You must specify the interest rate of the loan.", _
                   vbOKOnly Or vbInformation, _
                   "Whatts A Loan"
            Exit Sub
        End If
        
        If IsNull(Periods) Then
            MsgBox "You must specify the number of months (the period) of the loan.", _
                   vbOKOnly Or vbInformation, _
                   "Whatts A Loan"
            Exit Sub
        End If
        
        InterestAmount = FormatCurrency(CDbl(Nz(LoanAmount)) * CDbl(InterestRate) * (CDbl(Periods) / 12))
        FutureValue = FormatCurrency(CDbl(InterestAmount) + CDbl(LoanAmount))
        MonthlyPayment = FormatCurrency(CDbl(FutureValue) / CDbl(Periods))
    End Sub
  4. Close the LoansAllocations form
  5. When asked whether you want to save, click Yes
  6. Close Microsoft Access

Formatting a Number as a Percentage

A percentage of a number represents its rate on a scale, usually of 100 (or more). The number is expressed using digits accompanied by the % sign.

To programmatically use a percentage number in a cell or the control of a form, you can use the Format() function. Besides the Format() function, to support percent values, the Visual Basic language provides a function named FormatPercent. Its syntax is:

Function FormatPercent(
   ByVal Expression As Variant,
   Optional ByVal NumDigitsAfterDecimal As Integer = -1,
   Optional ByVal IncludeLeadingDigit As Integer = -2,
   Optional ByVal UseParensForNegativeNumbers As Integer = -2,
   Optional ByVal GroupDigits As Integer = -2
) As String

Only the first argument is required and it is the number that needs to be formatted. When calling this function, pay attention to the number you provide as argument. If the number represents a percentage value as a fraction of 0 to 1, make sure you provide it as such. An example would be 0.25. In this case, the Visual Basic interpreter would multiply the value by 100 to give the result. Here is an example:

Private Sub cmdFunction_Click()
    Dim DiscountRate As Double

    DiscountRate = 0.25
    MsgBox FormatPercent(DiscountRate)
End Sub

This would produce:

Percentage

If you pass the value in the hundreds, the interpreter would still multiply it by 100. Although it is not impossible to get a percentage value in the hundreds or thousands, you should make sure that's the type of value you mean to get.

Besides the FormatPercent() function, to format a number to its percentage equivalent, you can call the Format() function and pass the second argument as "Percent", "p", or "P". Here is an example:

Private Sub cmdFunction_Click()
    Dim DiscountRate As Double

    DiscountRate = 0.25
    MsgBox Format(DiscountRate, "Percent")
End Sub

Formatting a Currency Value

A currency value uses a special character specified in the Control Panel. In US English, this character would be the $ sign. To programmatically display the currency symbol in the result of a field or a text box on a form, you can simply add it as part of the second argument to the Format() function. Here is an example:

Private Sub cmdFunction_Click()
    Dim Number As Double

    Number = 205.5

    MsgBox Format(Number, "$###,#######.00")
End Sub

This would produce:

Format

Fortunately, there are more professional options. Besides the Format() function, to support currency formatting of a number, the Visual Basic language provides the FormatCurrency() function. Its syntax is:

Function FormatCurrency(
   ByVal Expression As Variant,
   Optional ByVal NumDigitsAfterDecimal As Integer = -1,
   Optional ByVal IncludeLeadingDigit As Integer = -2,
   Optional ByVal UseParensForNegativeNumbers As Integer = -2,
   Optional ByVal GroupDigits As Integer = -2
) As String

Only the first argument is required. It is the value that needs to be formatted. Here is an example:

Private Sub cmdFunction_Click()
    Dim UnitPrice As Double

    UnitPrice = 1450.5

    MsgBox FormatCurrency(UnitPrice)
End Sub

This would produce:

Format

Notice that, by default, the FormatCurrency() function is equipped to display the currency symbol (which, in US English is, the $ sign), the decimal separator (which in US English is the comma), and two decimal digits. If you want to control how many decimal digits are given to the result, pass a second argument as an integer. Here is an example:

Private Sub cmdFunction_Click()
    Dim UnitPrice As Double

    UnitPrice = 1450.5

    MsgBox FormatCurrency(UnitPrice, 4)
End Sub

This would produce:

Format

Instead of calling the FormatCurrency() function to format a number to currency, you can use the Format() function. If you do, pass it a second argument as "Currency", "c", or "C". Here is an example:

Private Sub cmdFunction_Click()
    Dim CarPrice As Double

    CarPrice = 42790

    MsgBox Format(CarPrice, "Currency")
End Sub

This would produce:

Format

 
 
   
 

Previous Copyright © 2002-2013 FunctionX, Inc. Next