Introduction to Built-In Functions
|
|
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
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
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 SubFunction 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 SubFunction 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 SubUsing 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:
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
Private Sub cmdFindIntegral_Click()
Dim dDecimal, iIntegral
dDecimal = CDbl(txtDecimalNumber)
iIntegral = Int(dDecimal)
txtIntegral = CStr(iIntegral)
End SubPrivate Sub cmdFixer_Click()
Dim dDecimal, iIntegral
dDecimal = CDbl(txtDoublePrecision)
iIntegral = Fix(dDecimal)
txtInteger = CStr(iIntegral)
End Sub
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
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
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
Private Sub cmdGenerate_Click()
Dim number
number = Rnd
txtRandomNumber = number
End Sub
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
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
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
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
Private Sub cmdInputBox_Click()
InputBox "Enter your name:"
End SubThe 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
Private Sub cmdInputBox_Click()
InputBox "Enter your name:", "Employment Application"
End Sub
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
Private Sub cmdInputBox_Click()
Dim employeeName
employeeName = InputBox("Enter your name:", "Employment Application")
txtEmployeeName = employeeName
End SubBasic 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:
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
and click the formPublic 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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
|
|
||
| Previous | Copyright © 2002-2022, FunctionX, Inc. | Next |
|
|
||