Home

Introduction to Built-In Functions

Fundamentals of Built-In Functions

Introduction

Microsoft Access and Microsoft Visual Basic ship with various functions and procedures you can use in your database. Since these functions exist already in the database system and in the programming environment, they are referred to as built-in.

Practical LearningPractical Learning: Introducing Built-In Functions

  1. Start Microsoft Access
  2. In the list of files, click Geometry1 from the previous lesson

Conversion Functions

Conversions functions are functions you use to convert a value from one type to another. The Visual Basic language provides a special function for each type. The common syntax of these functions is:

function-name(expression) As return-type

The expression can be of any kind. For example, it can be a string or expression that would produce a value such as the result of a calculation. The conversion function would take such a value, string, or expression and attempt to convert it. If the conversion is successful, the function would return a new value that is of the type specified by the return-type of our syntax.

To let you convert a value to a Byte value, the Visual Basic language provides a function named CByte. To do this, enter the value or the expression in the parentheses of CByte(). If the conversion is successful, CByte() produces a Byte value..

To convert a value to an integer, call the CInt() function.

To convert a value to a long integer, call the CLng() function.

To convert a value to a decimal number with single precision, call the CSng() function.

To convert a value to a decimal number with double-precision, call the CDbl() function.

If you want to convert a string to a monetary value, call the CCur() function.

These conversion functions can be resumed as follows:

Function  
Name Return Type Description
CByte Byte Converts an expression to Byte number
CDate Date Converts an expression to a date, a time, or a combination of date and time
CDbl Double Converts an expression to a floating-point number with double precision
CDec Decimal Converts an expression to a decimal number
CInt Integer Converts an expression to an integer (natural) number
CLng Long Converts an expression to a long integer (a large natural) number
CObj Object Converts an expression to an Object type
CSng Single Converts an expression to a floating-point number with single precision
CStr String Converts an expression to a string

These functions allow you to convert a known value to a another type.

Practical Learning: Converting Values

  1. On the Ribbon, click Create and click Visual Basic
  2. In the Project window, double-click Form_Quadrilateral - Rectangle
  3. Change the document as follows:
    Sub SolveRectangle()
        ' Declare the necessary variables for the rectangle
        Dim dblWidth As Double, dblHeight As Double
        Dim dblPerimeter, dblArea As Double
        
        ' Retrieve the values of the sides
        dblWidth = CDbl(txtWidth)
        dblHeight = CDbl(txtHeight)
        
        ' Calculate the perimeter and the area of the rectangle
        dblPerimeter = (dblWidth + dblHeight) * 2
        dblArea = dblWidth * dblHeight
    
        ' Prepare to display the result in the appropriate text boxes
        txtPerimeter = CStr(dblPerimeter)
        txtArea = CStr(dblArea)
    End Sub
    
    Private Sub cmdCalculate_Click()
        SolveRectangle
    End Sub
  4. In the Project window, double-click Geometric Volume - Cube and change the document as follows:
    Function CalculateSingleArea(side As Double) As Double
        CalculateSingleArea = side * side * 6
    End Function
    
    Function CalculateTotalArea(side As Double) As Double
        CalculateTotalArea = CalculateSingleArea(side) * 6
    End Function
    
    Function CalculateVolume(side As Double) As Double
        CalculateVolume = CalculateSingleArea(side) * side
    End Function
    
    Private Sub cmdCalculate_Click()
        Dim s As Double
        Dim sa As Double
        Dim ta As Double
        Dim vol As Double
        
        s = CDbl(txtSide)
        sa = CalculateSingleArea(s)
        ta = CalculateTotalArea(s)
        vol = CalculateVolume(s)
        
        txtSingleArea = CStr(sa)
        txtTotalArea = CStr(ta)
        txtVolume = CStr(vol)
    End Sub
  5. In the Project window, double-click Form_Geometric Box and cchange the document as follows:
    Function CalculateArea(dblLength As Double, _
                           dblHeight As Double, _
                           dblWidth As Double) As Double
        Dim area As Double
        
        area = 2 * ((dblLength * dblHeight) + _
                    (dblHeight * dblWidth) + _
                    (dblLength * dblWidth) _
                   )
        CalculateArea = area
    End Function
    
    Function CalculateVolume(dblLength As Double, _
                             dblHeight As Double, _
                             dblWidth As Double) As Double
        Dim volume As Double
        
        volume = dblLength * dblHeight * dblHeight
        CalculateVolume = volume
    End Function
    
    Private Sub cmdCalculate_Click()
        Dim area As Double, volume As Double
        Dim dWidth As Double, dHeight As Double, dDepth As Double
        
        dWidth = CDbl(txtWidth)
        dHeight = CDbl(txtHeight)
        dDepth = CDbl(txtDepth)
        
        area = CalculateArea(dWidth, dHeight, dDepth)
        volume = CalculateVolume(dWidth, dHeight, dDepth)
        
        txtArea = CStr(area)
        txtVolume = CStr(volume)
    End Sub
  6. Return to Microsoft Access
  7. Save and close the forms

Using Colors

Many of the aesthetic characteristics of an object (tables, forms, reports, and controls) use colors. The color provides an enhanced variation of the ratios of red, green, and blue applied to the appearance of an object. The Visual Basic language supports colors at different levels.

In Microsoft Windows, a color is a long integer whose value ranges from 0 to 16777216. In many cases, to use a color, if you know the exact value it represents, you can assign it to the property.

To let you programmatically create a color if you know its variances of red, green, and blue, the Visual Basic language provides a function named RGB. Its syntax is:

Function RGB(RedValue As Byte, GreenValue As Byte, BlueValue As Byte) As long

This function takes three arguments and each must hold a value between 0 and 255:

  • The first argument represents the ratio of red of the color
  • The second argument represents the green ratio of the color
  • The last argument represents the blue of the color.

After the function has been called, it produces a number whose maximum value can be 256 (that is, the number ranges from 0 to 255 included) * 256 * 256 = 16,777,216, which represents a color.

The Integral Part of a Decimal 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 two functions named Int and Fix. Their syntaxes are:

Public Function Int( _
    ByVal Number As { Double | Integer | Long | 
		      Object | Single | Variant })
    As { Double | Integer | Long | Object | Single | Variant}
Public Function Fix( _
    ByVal Number As { Double | Integer | Long | 
		      Object | Single | Variant })
    As { Double | Integer | Long | Object | Single | Variant }

Each function must take one argument. The value of the argument must be number-based.

Practical Learning: Getting the Integral Part of a Decimal Number

  1. On the Ribbon, click File and click Open
  2. From the resources that accompany these lessons, open the Business Mathematics database
  3. In the Navigation Pane, right-click Fixed Integral and click Design View
  4. In the Tools section of the Ribbon, click the View Code button View Code
  5. In the Object combo box, select cmdFindIntegral and implement the event as follows:
    Private Sub cmdFindIntegral_Click()
        Dim dDecimal, iIntegral
        
        dDecimal = CDbl(txtDecimalNumber)
        iIntegral = Int(dDecimal)
        
        txtIntegral = CStr(iIntegral)
    End Sub
  6. In the Object combo box, select cmdFixer and implement the event as follows:
    Private Sub cmdFixer_Click()
        Dim dDecimal, iIntegral
        
        dDecimal = CDbl(txtDoublePrecision)
        iIntegral = Fix(dDecimal)
        
        txtInteger = CStr(iIntegral)
    End Sub
  7. Return to Microsoft Access
  8. Click Decimal Number and type a decimal number such as 1857.802
  9. Click the Integrate button
  10. Click Double-Precision and type a decimal number such as 1857.802
  11. Click the Fix button

    Getting the Integral Part of a Decimal Number

  12. Save and close the form

The Memory Used by a Data Type

To let you get the amount of space that a data type or a variable uses or needs, the Visual Language provides a function named Len . Its syntax is:

Public Function Len( _
   ByVal Expression As { Boolean | Byte | Double |
   Integer | Long | Object | Single | String | Date | Variant } _
) As Integer

To call this function, you can declare a variable with a data type of your choice and optionally initialize it with the appropriate value, then pass that variable to the function.

Practical Learning: Getting the Memory Length of a Data Type

  1. In the Navigation Pane, right-click Data Types and click Design View
  2. In the Tools section of the Ribbon, click the View Code button View Code
  3. In the Object combo box, select Form and implement the Load event as follows:
    Private Sub Form_Load()
        Dim a As Byte
        Dim b As Boolean
        Dim c As Integer
        Dim d As Long
        Dim e As Single
        Dim f As Double
        Dim g As String
        Dim h As Date
        Dim i As Variant
    
        txtByte = Len(a) & " Byte"
        txtBoolean = Len(b) & " Bytes"
        txtInteger = Len(c) & " Bytes"
        txtLong = Len(d) & " Bytes"
        txtSingle = Len(e) & " Bytes"
        txtDouble = Len(f) & " Bytes"
        txtString = Len(g) & " Bytes"
        txtDate = Len(h) & " Bytes"
        txtVariant = Len(i) & " Bytes"
    End Sub
  4. Return to Microsoft Access and switch the form to Form View

    Lengths of Visual Basic Data Types

  5. Save and close the form

The Beeping Sound

If you want, you can make the computer produce a beeping a sound in response to something, anything. To support this, the Visual Basic language provides a function called Beep. Its syntax is:

Public Sub Beep()

Here is an example of calling it:

Private Sub cmdBeep_Click()
    Beep
End Sub

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

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. 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)
    Let 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)
    Let txtValue = intValue%
End Sub

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

Random Numbers

A number is referred to as random if it is generated from a pool but without a specific pattern to follow.

To assist you with getting a random number, the Visual Basic language provides a function named Rnd. Its syntax is:

Public Function Rnd[(Number)] As Single

This function takes an optional argument. If the argument is not passed, the function generates a positive decimal number between 0 and 1.

To get a random number between 1 and a certain maximum number, you can use the following formula:

Practical Learning: Generating Random Numbers

  1. In the Navigation Pane, right-click Random1 and click Design View
  2. On the form, right-click the Generate button and click Build Event
  3. In the Choose Builder dialog box, double-click Code Builder and implement the event as follows:
    Private Sub cmdGenerate_Click()
        Dim number
        
        number = Rnd
        
        txtRandomNumber = number
    End Sub
  4. Return to Microsoft Access and switch the form to Form View
  5. Click the Generate button

    Random Numbers

  6. Click the Generate button again

    Random Numbers

  7. Save and close the form
  8. In the Navigation Pane, right-click Random2 and click Design View
  9. On the form, right-click the Generate button and click Build Event
  10. In the Choose Builder dialog box, double-click Code Builder
  11. If you want a number outside of 0 and 1, all you have to do is to multiply the random number by a factor of your choice.
    For an example, implement the event as follows:
    Private Sub cmdGenerate_Click()
        Dim number
        Dim unitPrice
        Dim prior
        Dim yearlySalary
    
        number = Rnd
        unitPrice = number * 100
        prior = number * -3609
        yearlySalary = number * 100000
    
        txtRandomNumber = number
        txtUnitPrice = unitPrice
        txtNegativeNumber = prior
        txtYearlySalary = yearlySalary
    End Sub
  12. Return to Microsoft Access and switch the form to Form View
  13. Click the Generate button:

    Random Numbers

  14. Click the Generate button again to see different numbers
  15. Save and close the form
  16. In the Navigation Pane, right-click Random3 and click Design View
  17. On the form, right-click the Generate button and click Build Event
  18. In the Choose Builder dialog box, double-click Code Builder
  19. If you want a natural number, you can pass the multiplied number to the Int() or the Fix() function. For an example, implement the event as follows:
    Private Sub cmdGenerate_Click()
        Dim number
        Dim unitPrice
        Dim population
        Dim yearlySalary, prior
    
        number = Rnd
        prior = number * -3609
        yearlySalary = number * 100000
        population = Int(number * 1000000)
    
        txtRandomNumber = number
        txtPopulation = population
        txtNegativeNumber = Int(prior)
        txtYearlySalary = Fix(yearlySalary)
    End Sub
  20. Return to Microsoft Access and switch the form to Form View
  21. Click the Generate button:

    Random Numbers

  22. Click the Generate button again

    Random Numbers

  23. Save and close the form
  24. In the Navigation Pane, right-click Random4 and click Design View
  25. On the form, right-click the Generate button and click Build Event
  26. In the Choose Builder dialog box, double-click Code Builder
  27. If you want a natural number, you can pass the multiplied number to the Int() or the Fix() function. For an example, implement the event as follows:
    Private Sub cmdGenerate_Click()
        Dim number As Long
        
        number = CInt(Int((10 * Rnd()) + 1))
     
        txtNumber1 = number
    
        number = CInt(Int((1000 * Rnd()) + 1))
    
        txtNumber2 = number
    
        number = CInt(Int((10000 * Rnd()) + 1))
    
        txtNumber3 = number
    End Sub
  28. Return to Microsoft Access and switch the form to Form View
  29. Click the Generate button:

    Random Numbers

  30. Click the Generate button again

    Random Numbers

  31. Save and close the form

The Input Box

Introduction

The Visual Basic language provides a function that allows you to request information from the user who can type it in a text field of a dialog box:

An Input Box

Creating an Input Box

The function used to request a value is called InputBox and its syntax is:

Public Function InputBox( _
   ByVal Prompt As String, _
   Optional ByVal Title As String = "", _
   Optional ByVal DefaultResponse As String = "", _
   Optional ByVal Xpos As Integer = -1, _
   Optional ByVal YPos As Integer = -1 _
) As String

The Message of an Input Box

The primary piece of information you can provide to an input box is referred to as the prompt. It should be some text the user will read to know what you are expecting. Upon reading the message on the input box, the user is asked to enter a piece of information. It can be a regular message. Here is an example:

 InputBox("Please enter your date of birth as mm/dd/yyyy")

You can also provide an example to the user.

Practical Learning: Introducing the Input Box

  1. In the Navigation Pane, right-click Input and click Design View
  2. On the form, right-click the Input Box button and click Build Event
  3. In the Choose Builder dialog box, double-click Code Builder and implement the event as follows:
    Private Sub cmdInputBox_Click()
        InputBox "Enter your name:"
    End Sub
  4. Return to Microsoft Access and switch the form to Form View
  5. Click the button
  6. Type a name in the text box and click OK
  7. Return to Microsoft Visual Basic

The Title of an Input Box

The second argument to the InputBox() function allows you to optionally specify the title of the input box. This is text that would appear on the title bar. Since this is an optional argument, if you don't pass it, the input box would display the name of the application. Otherwise, to display your own title bar, pass the Title argument.

The caption can be text created from an expression or from a variable or value.

Practical Learning: Introducing the Title of an Input Box

  1. Change the code as follows:
    Private Sub cmdInputBox_Click()
        InputBox "Enter your name:", "Employment Application"
    End Sub
  2. Return to Microsoft Access and click the button

    The Title of an Input Box

  3. Type a name in the text box and click OK
  4. Return to Microsoft Visual Basic

The Default Value of an Input Box

You can provide an example to the user by filling the text box with a default value. To support this, the InputBox() function provides the third argument. Here is an example:

Private Sub cmdInputBox_Click()
    InputBox "Enter Student Name:", _
             "Student Registration", "John Doe"
End Sub

Here is an example of running the program:

The Default Value of an Input Box

When the input box displays with a default value, the value is in the text box and the value is selected. Therefore, if the value is fine, the user can accept it and click OK. Another way you can use the default value is to provide a value the user can accept; that is, the most common or most likely value the user would enter. Here is an example:

Private Sub cmdInputBox_Click()
    InputBox "Enter Birth State:", _
             "Student Registration", "VA"
End Sub

The Location of the Input Box

By default, when the input box comes up, it displays in the middle of the screen. If you want, you can specify where the input box should be positioned when it comes up. To assist you with this, the InputBox() function is equipped with a fourth and a fifth arguments. The fourth argument specifies the x coordinate of the input box. The fifth argument specifies the distance from the top border of the input box to the top border of the monitor.

The Return Value of an Input Box

When the input box displays, after typing a value, the user can click one of the buttons: OK or Cancel. If the user clicks OK, you can retrieve the value the user had typed. It is also your responsibility to find out whether the user typed a valid value. Because the InputBox() function can return any type of value, it has no mechanism of validating the user's entry. To retrieve the value of the input box when the user clicks OK, you can get the returned value of the InputBox() function.

After being used, the InputBox() function returns a string. You can also get any type of value from an input box. That is, when the InputBox() function exits, thanks to the flexibility of the Visual Basic language, the compiler can directly cast the returned value for you.

Practical Learning: Getting the Return Value of an Input Box

  1. Change the code as follows:
    Private Sub cmdInputBox_Click()
        Dim employeeName
        
        employeeName = InputBox("Enter your name:", "Employment Application")
        
        txtEmployeeName = employeeName
    End Sub
  2. Return to Microsoft Access and click the button
  3. Type a name in the text box and press Enter
  4. Return to Microsoft Visual Basic

Basic Algebra in Microsoft Visual Basic

The Absolute Value of a Number

The absolute value of a number x is x if the number is (already) positive. If the number is negative, 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 get 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

The Square Root of a Number

To let you get the square root of a number, the Visual Basic language provides a function named Sqr. Its syntax is:

Public Function Sqr(ByVal number As Double) As Double

Practical Learning: Calculaing the Square Root of a Number

  1. In the Navigation Pane, right-click Octagon and click Design View
  2. In the Controls section of the Ribbon, click Image Image and click the form
  3. From the resources that accompany these lessons, select Octagon
  4. On the form, right-click the Calculate button and click Build Event
  5. In the Choose Builder dialog box, click Code Builder and click OK
  6. Implement the event as follows:
    Public Function CalculatePerimeter(ByVal value As Double) As Double
        CalculatePerimeter = value * 8#
    End Function
    
    Public Function CalculateArea(ByVal value As Double) As Double
        CalculateArea = value * value * 2# * (1# + Sqr(2#))
    End Function
        
    Private Sub cmdCalculate_Click()
        Dim side As Double
        Dim perimeter, area
    
        side = CDbl(txtSide)
    
        perimeter = CalculatePerimeter(side)
        area = CalculateArea(side)
            
        txtPerimeter = perimeter
        txtArea = area
    End Sub
  7. Return to Microsoft Access and switch the form to Form View
  8. Click Side and type a positive number such as 35.96
  9. Click the Calculte button

    Calculaing the Square Root of a Number

  10. Save and close the form
 
 
 

Business Mathematics: Borrowing Money/Financing an Item

The Regular Payments of a Loan

To let you evaluate the payment to be made for each period of a loan, the Visual Basic language provides a function named Pmt. Its syntax is:

Public Function Pmt(Rate As Double,
		    NPer As Double,
		    PV As Double,
		    FV As Double,
		    Due As Variant) As Double

Practical Learning: Calculaing the Payments of a Loan

  1. In the Navigation Pane, right-click Loan Financing and click Design View
  2. On the form, right-click the Calculate button and click Build Event
  3. In the Choose Builder dialog box, click Code Builder and click OK
  4. Implement the event as follows:
    Private Sub cmdCalculate_Click()
        Dim periods
        Dim payment
        Dim interestRate
        Dim loanAmount
        
        loanAmount = txtLoanAmount
        interestRate = CDbl(txtInterestRate) / 100#
        periods = txtPeriods
    
        payment = Pmt(interestRate / 12#, periods, -loanAmount, 0#, 1)
    
        txtLoanPayment = payment
    End Sub
  5. Return to Microsoft Access and switch the form to Form View

    The Regular Payments of a Loan

  6. Click Loan Amount and type a decimal number such as 2450
  7. Click Interest Rate and type a decimal number such as 12.50
  8. Click Number of Periods and type a natural number such 48

    The Regular Payments of a Loan

  9. Click the Calculate button:

    The Regular Payments of a Loan

  10. Save and close the form

The Payment Applied to the Principal

To let you calculate the portion of the payment that applies to the principal, the Visual Basic language provides the PPmt function. Its syntx is:

Public Function PPmt(Rate As Double,
		     Per As Double,
		     NPer As Double,
		     PV As Double,
		     FV As Double,
		     Due As Variant) As Double

Practical Learning: Calculating the Principal Payment of a Loan

  1. In the Navigation Pane, right-click Principal Payment and click Design View
  2. On the form, right-click the Calculate button and click Build Event
  3. In the Choose Builder dialog box, click Code Builder and click OK
  4. Implement the event as follows:
    Private Sub cmdCalculate_Click()
        Dim periods
        Dim interestRate
        Dim regularPayment
        Dim periodicPayment
        Dim principalPayment
        
        loanAmount = txtLoanAmount
        interestRate = CDbl(txtInterestRate) / 100#
        periods = txtPeriods
    
        periodicPayment = Pmt(interestRate / 12#, periods, -loanAmount, 0#, 1)
        principalPayment = PPmt(interestRate / 12#, 1, periods, -loanAmount, 0#, 1)
    
        txtLoanPayment = loanAmount
        txtPrincipalPayment = principalPayment
    End Sub
  5. Return to Microsoft Access and switch the form to Form View

    Calculating the Principal Payment of a Loan

  6. Click Loan Amount and type a decimal number such as 32500
  7. Click Interest Rate and type a decimal number such as 12.35
  8. Click Number of Periods and type a natural number such 60

    Calculating the Principal Payment of a Loan

  9. Click the Calculate button:

    Calculating the Principal Payment of a Loan

  10. Save and close the form

The Interest Paid on a Loan

To let you calculate the amount of payment that covers the interest portion of a loan (or of an annuity), the Visual Basic language provides a function named IPmt. Its syntax is:

Public Function IPmt(Rate As Double,
		     Per As Double,
		     NPer As Double,
		     PV As Double,
		     FV As Double,
		     Due As DueDate) As Double

Practical Learning: Calculating the Interest Payment of a Loan

  1. In the Navigation Pane, right-click Interest Payment and click Design View
  2. On the form, right-click the Calculate button and click Build Event
  3. In the Choose Builder dialog box, click Code Builder and click OK
  4. Implement the event as follows:
    Private Sub cmdCalculate_Click()
        Dim periods
        Dim payment
        Dim loanAmount
        Dim interestPaid
        Dim interestRate
    
        interestRate = CDbl(txtInterestRate) / 100#
        periods = txtPeriods
        loanAmount = txtLoanAmount
    
        interestPaid = IPmt(interestRate / 12#, 1, periods, -loanAmount, 0#, 0)
        payment = Pmt(interestRate / 12#, periods, -loanAmount, 0#, 0)
    
        txtLoanPayment = payment
        txtInterestPaid = interestPaid
    End Sub
  5. Return to Microsoft Access and switch the form to Form View

    Calculating the Principal Payment of a Loan

  6. Click Loan Amount and type a decimal number such as 24500
  7. Click Interest Rate and type a decimal number such as 7.55
  8. Click Number of Periods and type a natural number such 60

    Calculating the Principal Payment of a Loan

  9. Click the Calculate button:

    Calculating the Principal Payment of a Loan

  10. Save and close the form

Business Mathematics: Saving Money or Investing

The Amount of Money to Invest

Imagine that a customer wants to reach a certain amount of money in the money through a savings account or an investment. To let you figure out the amount the customer should invest now, the Visual Basic language provides a function named PV (which stands for present value). Its syntax is:

Public Function PV(Rate As Double,
		   NPer As Double,
		   Pmt As Double,
		   FV As Double,
		   Due As DueDate) As Double

Practical Learning: Calculating the Amount to Invest for a Loan

  1. In the Navigation Pane, right-click Money Investment and click Design View
  2. On the form, right-click the Calculate button and click Build Event
  3. In the Choose Builder dialog box, click Code Builder and click OK
  4. Implement the event as follows:
    Private Sub cmdCalculate_Click()
        Dim periods
        Dim payment
        Dim futureValue
        Dim interestRate
        Dim presentValue
        
        interestRate = CDbl(txtInterestRate) / 100#
        periods = CDbl(txtPeriods)
        payment = CDbl(txtRegularAmount)
        futureValue = CDbl(txtFutureValue)
    
        presentValue = PV(interestRate / 12#, periods, -payment, futureValue, 1)
    
        txtPresentValue = presentValue
    End Sub
  5. Return to Microsoft Access and switch the form to Form View

    Calculating the Amount to Invest for a Loan

  6. Click Interest Rate and type a decimal number such as 2.25
  7. Click Number of Periods and type a natural number such 60
  8. Click Regular Deposit Amount and type a number such as 150
  9. Click Future Value and type a number such as 6500

    Calculating the Amount to Invest for a Loan

  10. Click the Calculate button:

    Calculating the Amount to Invest for a Loan

  11. Save and close the form

The Future Value of an Investment or a Purchase

To let you estimate the future value or an investment, a purchased machine, or from financing something (such as borrowing money to finance a car, etc), the Visual Basic library provides a function named FV. Its syntax is:

Public Function FV(Rate As Double,
		   NPer As Double,
		   Pmt As Double,
		   PV As Double,
		   Due As DueDate) As Double

Practical Learning: Calculating the Future Amount to Invest

  1. In the Navigation Pane, right-click Money Investment and click Design View
  2. On the form, right-click the Calculate button and click Build Event
  3. In the Choose Builder dialog box, click Code Builder and click OK
  4. Implement the event as follows:
    Private Sub cmdCalculate_Click()
        Dim periods
        Dim payment
        Dim futureValue
        Dim interestRate
        Dim presentValue
            
        interestRate = CDbl(txtInterestRate) / 100#
        periods = txtPeriods
        payment = txtPayment
        presentValue = txtPresentValue
            
        futureValue = FV(interestRate / 12#, periods, -payment, -presentValue, 1)
    
        txtFutureValue = FormatNumber(futureValue)
    End Sub
  5. Return to Microsoft Access and switch the form to Form View

    Calculating the Future Amount to Invest

  6. Click Advance Payment and type a number such as 1000
  7. Click Interest Rate and type a decimal number such as 3.25
  8. Click Number of Periods and type a natural number such 48
  9. Click Payment Payment and type a number such as 250

    Calculating the Future Amount to Invest

  10. Click the Calculate button:

    Calculating the Future Amount to Invest

  11. Save and close the form

Business Mathematics: Depreciation

The Straight-Line Method

To let you calculate the yearly depreciation of a machine using the straight-line method, the Visual Basic language provides a function named SLN. Its syntax is:

Function SLN(ByVal Cost As Double,
	     ByVal Salvage As Double,
	     ByVal Life As Double) As Double

All three arguments are required.

Practical Learning: Calculating Depreciation Using the Straight-Line Method

  1. In the Navigation Pane, right-click Straight-Line Method1 and click Design View
  2. On the form, right-click the Calculate button and click Build Event
  3. In the Choose Builder dialog box, click Code Builder and click OK
  4. Implement the event as follows:
    Private Sub cmdCalculate_Click()
        Dim cost
        Dim salvageValue
        Dim estimatedLife
        Dim depreciation
    
        cost = CDbl(txtCost)
        salvageValue = CDbl(txtSalvageValue)
        estimatedLife = CDbl(txtEstimatedLife)
    
        depreciation = SLN(cost, salvageValue, estimatedLife)
    
        txtDepreciation = CStr(depreciation)
    End Sub
  5. Return to Microsoft Access and switch the form to Form View

    Calculating Depreciation Using the Straight-Line Method

  6. Click Asset Original Value and type a number such as 25800
  7. Click Salvage Value and type a number such as 5000
  8. Click Estimated Life and type a natural number such 5

    Calculating Depreciation Using the Straight-Line Method

  9. Click the Calculate button:

    Calculating Depreciation Using the Straight-Line Method

  10. Close the form
  11. When asked whether you want to save, click No

The Double-Declining Balance

To let you calculate the double-declining balance of a machine, the Visual Basic language provides a function named DDB. Its syntax is:

Public Function DDB(Cost As Double,
		    Salvage As Double,
		    Life As Double,
		    Period As Double,
		    Factor As Double) As Double

Practical Learning: Calculating Depreciation Using the Double-Declining Balance

  1. In the Navigation Pane, right-click Straight-Line Method and click Design View
  2. On the form, right-click the Calculate button and click Build Event
  3. In the Choose Builder dialog box, click Code Builder and click OK
  4. Implement the event as follows:
    Private Sub cmdCalculate_Click()
        Dim cost
        Dim depreciationRate
        Dim estimatedLife
        Dim period
        Dim factor
        Dim depreciation
            
        cost = CDbl(txtCost)
        depreciationRate = CDbl(txtDepreciationRate)
        estimatedLife = CDbl(txtEstimatedLife)
        period = CDbl(txtPeriod)
        factor = CDbl(txtFactor)
            
        depreciation = DDB(cost, depreciationRate, estimatedLife, period, factor)
        txtDepreciation = CStr(depreciation)
    End Sub
  5. Return to Microsoft Access and switch the form to Form View

    Calculating Depreciation Using the Double-Declining Balance

  6. Click Original Value and type a number such as 17000
  7. Click Depreciation Rate and type a number such as 40
  8. Click Estimated Life and type a natural number such 5
  9. Click Period and type a natural number such 1
  10. Click Factor and type a natural number such 2

    Calculating Depreciation Using the Double-Declining Balance

  11. Click the Calculate button:

    Calculating Depreciation Using the Double-Declining Balance

  12. Save and close the form

Sum-of-Years Digits

To let you evaluatethe depreciation of a machine based on the sum-of-years digits technique, the Visual Basic language provides a function named SYD. Its syntax is:

Public Function SYD(Cost As Double,
		    Salvage As Double,
		    Life As Double,
		    Period As Double) As Double

The Win32 API

Introduction

Most of the built-in functions we will use are from the Visual Basic language. Besides those functions, the Microsoft Windows operating system provides its own set of functions and objects. The library is called the Win32 Application Programming Interface or Win32 API, or simply Win32. The Win32 library is somehow available to all applications but its functions are not directly available for a database. The functions are stored in various sub-libraries named dynamic link libraries (DLLs).

Using Win32

Before using a Win32 function in your code, you must first have two pieces of information: the DLL in which the function was created and the actual name of the desired function in that library. Examples of DLLs are shfolder or Kernel32. Once you know the name of the library and the name of the function you want to use, you must "import" it in your Visual Basic code. The basic formula to follow is:

Private Declare Function Win32-function-name Lib "library-name"
	Alias "custom-name" (parameter(s)) As data-type

The Win32-function-name is the name of the function in the Win32 library. The library-name is the name of the library. You can specify a custom name for the function as the custom-name factor. In the parentheses, enter the names and types of the parameters. If the procedure returns a value, you can specify its type after the As keyword.

Here is an example:

Option Compare Database
Option Explicit

Private Const MAX_PATH = 260
Private Const CSIDL_PERSONAL = &H5&
Private Const SHGFP_TYPE_CURRENT = 0

' We will use the Windows API to get the path to My Documents
Private Declare Function SHGetFolderPath Lib "shfolder" _
    Alias "SHGetFolderPathA" _
    (ByVal hwndOwner As Long, ByVal nFolder As Long, _
    ByVal hToken As Long, ByVal dwFlags As Long, _
    ByVal pszPath As String) As Long

Private Sub cmdCreateDatabase_Click()
    Dim strMyDocuments As String
    Dim strDbName As String
    Dim valReturned As Long
    Dim dbMVD As DAO.Database
    
    ' Initialize the string
    strMyDocuments = String(MAX_PATH, 0)
    
    ' Call the Shell API function to get the path to My Documents
    ' and store it in the strMyDocuments folder
    valReturned = SHGetFolderPath(0, CSIDL_PERSONAL, _
                                  0, SHGFP_TYPE_CURRENT, strMyDocuments)
    ' "Trim" the string
    strMyDocuments = Left(strMyDocuments, InStr(1, strMyDocuments, Chr(0)) - 1)
    ' Include the name of the database in the path
    strDbName = strMyDocuments & "\Motor Vehicle Division.mdb"
    
    ' Create the database
    Set dbMVD = CreateDatabase(strDbName, dbLangGeneral)
End Sub

Practical Learning: Ending the Lesson

  • Close Microsoft Access
 
 
   
 

Previous Copyright © 2002-2016, FunctionX, Inc. Next