Introduction to BuiltIn Functions 

Fundamentals of BuiltIn 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 builtin.
Practical Learning: Introducing BuiltIn 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:
functionname(expression) As returntype
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 returntype 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 doubleprecision, 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 floatingpoint 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 floatingpoint 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 Sub
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
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
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:
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 numberbased.
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 Sub
Private 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 builtin 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 Sub
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
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 Sub
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:
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
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
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 StraightLine Method
To let you calculate the yearly depreciation of a machine using the straightline 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 StraightLine 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 DoubleDeclining Balance
To let you calculate the doubledeclining 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 DoubleDeclining 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
SumofYears Digits
To let you evaluatethe depreciation of a machine based on the sumofyears 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 builtin 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 sublibraries 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 Win32functionname Lib "libraryname" Alias "customname" (parameter(s)) As datatype
The Win32functionname is the name of the function in the Win32 library. The libraryname is the name of the library. You can specify a custom name for the function as the customname 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 © 20022022, FunctionX, Inc.  Next 
