 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 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 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 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 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 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 6. Click the Generate button again 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: 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: 22. Click the Generate button again 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: 30. Click the Generate button again 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: 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 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: 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 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 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 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 9. Click the Calculate button: 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 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 9. Click the Calculate button: 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 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 9. Click the Calculate button: 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 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 10. Click the Calculate button: 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 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 10. Click the Calculate button: 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 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 9. Click the Calculate button: 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 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 11. Click the Calculate button: 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