Introduction to BuiltIn Functions 

Accessory Functions and Procedures 
In our introduction to procedures in Lesson 5, we saw different ways or creating modules, subprocedures and functions. Before creating a new function that perform a specific task, first find out if that function exists already. The library built in Microsoft Access is very large (one of the largest you will ever see in the industry) and provides functions that cover many subjects, including general topics, algebra, conversion, finance, accounting, date, time, and strings, etc. The available functions are highly reliable so you can safely use them. 
When an expression is supposed to produce a numeric value, it is important to make sure you get that value before involving it in another operation. Microsoft Office provides a function that can be used to easily get the numeric value of an expression. The function is called Val and its syntax is: Val(Expression) In reality, this function can be considered as two in one. In other words, it can produce either a natural or a real number. This function takes as argument either an unknown value or an expression, such as an algebraic calculation. In most cases, or whenever possible, you should be able to predict the type of expression passed as argument. For example, if you pass an algebraic operation that calculates the sum of two natural numbers, you should be able to predict that the function would return a natural number. In this case, you can retrieve the integer that the function returns. Here is an example: Private Sub cmdValue_Click() Dim intValue% intValue% = Val(145 + 608) txtValue = intValue% End Sub In the same way, you can ask this function to perform an algebraic operation on two or more natural numbers, two or more decimal numbers, two or more numbers that include at least one decimal number. If the function receives an operation that involves two natural numbers, it would return a natural number. If the function receives an operation that involves at least one decimal number and one or more natural numbers, the function would return a decimal number. If the function receives an operation that involves decimal numbers, it would produce a decimal number. Regardless of the types of numbers that this function receives, you still can impose the type of value you want to retrieve. If the function receives an operation that involves only natural numbers, you may prefer to get a decimal number from it. If the function receives an operation that involves at least one decimal number and one or more natural numbers, you can still retrieve only the natural number. Here is an example: Private Sub cmdValue_Click() Dim intValue% intValue% = Val(455 + 1250.85 + 88) txtValue = intValue% End Sub This call of the Val() function would return a decimal number but you mange to retrieve the natural number:
So far, after performing a calculation, we were displaying the result "as is". To appropriately display a value, Microsoft Visual Basic provides the Format() function. Although it can be used for different types of values, the most basic technique consists of passing it an expression that holds the value to display. In this case the syntax to use would be: FormatNumber(Expression [,NumDigitsAfterDecimal _ [,IncludeLeadingDigit _ [,UseParensForNegativeNumbers _ [,GroupDigits]]]]) To format a value or an expression into a currency or monetary value, you can call the FormatCurrency() function. Its syntax is: FormatCurrency(Expression[,NumDigitsAfterDecimal _ [,IncludeLeadingDigit _ [,UseParensForNegativeNumbers _ [,GroupDigits]]]]) The first argument, Expression, is required. It can be a value or an expression. It can be a natural number or a decimal value with single or doubleprecision. An example is: FormatCurrency(12.5) This would produce $12.50 if the application runs on a US English computer. The argument can also be passed as an expression. Here is an example: FormatCurrency([Total Hours In Week] * [Hourly Salary]) In this case, the value of the first operand would be multiplied to that of the second operand of the expression. The result should be a currency value. The second argument, optional, is the desired number of digits after the decimal separator. In US English, this value should be 2. Most of the time, you should ignore it. The third argument, also optional, allows you to specify whether to display a leading zero, such as 012.55 instead of the regular 12.55. the fourth argument, also optional, allows you to indicate if the result, when negative, should be displayed between parentheses. Normally, in Latin languages such as English, a negative value is indicated by a  sign to its left. The last argument, also optional, allows you to display the values in thousands to make it easy to easy. For example, if you ignore this argument, you may get a number such as 81047576.50 but if you pass this argument as True, this number would display as 81,047,576.50.
Besides the Val() and the Eval() function that we saw earlier, Microsoft Access is equipped with functions used to perform routine arithmetic and algebraic operations. It also provides functions used in collections of values. Some functions can "scan" a series of records of a table or a form and provide the appropriate value. In arithmetic, we are used to calculating the sum of a series of values. In a database, you can also calculate the sum of the values that a certain column holds. This operation can be carried by the Sum() function. Its syntax is: Sum(FieldName) This function takes one argument. The argument can be the name of a column. If it is, all the values held by that column, throughout the records of the table, would be summed up and this function would return the result. The argument to this function can also be an expression. It can even be a combination of columns. Besides the Sum() function, another arithmeticoriented function you can use is Count() and it uses the same syntax as Sum(). The Count() function is used to count the number of entries of a column among the records of a table. Here is an example of using it: =Count([InvoiceID]) In this case, this would return the number of records of the table. The Avg() function is used to calculate the average value of the values of a column. It uses the same syntax as the Sum() function. The Min() (or the Max()) function is used to calculate the minimum (or the maximum) of the values of a column. It uses the same syntax as the Sum() function. The First() (or the Last()) function is used to find the first (or the last) value of a column among the records of a table.
Microsoft Access and the Microsoft Visual Basic language are equipped with various functions used to manipulate date and time values. At the most basic level, you can use the Date(), Time(), and Now() functions to display their values. The Date() function is used to get the system date of the computer. To display the system date in a text box, you can enter =Date() in its Control Source property. The Time() function is used to get the system time of the computer. To display the system time in a text box, you can enter =Time() in its Control Source property. The Now() function combines the system date and time of the computer.
The Day() function is used to get the numeric value that represents a day in the month. It ranges from 1 to 31 included. The syntax of the Day() function is Day(DateValue) The Month() function displays the numeric month of a date. It ranges from 1 to 12 included. The formula of the Month function is Month(DateValue) The Year() function returns the numeric year of a date. The syntax of this function is Year(DateValue)
The DateAdd() function can be used to add an interval value, such as a number of days, weeks, months, or years to another date. Its syntax is: DateAdd(Interval, Number, date) Required, the Interval argument specifies what kind of value you want as a result. This argument will be enclosed between double quotes and can have one of the following values:
Required also, the Number argument specifies the number of units you want to add. If you set it as positive, its value will be added. If you want to subtract, pass it as a negative value. The number represents how many units of the Interval argument you want to add. The date argument is the date to which you want to add the number.
The DateDiff() function is used to find the difference between two date or time values. It allows you to find the number of seconds, minutes, hours, days, weeks, months, or years when you supply two recognizable values. The DateDiff() function takes 5 arguments, 3 are required and 2 are optional. The syntax of the DateDiff() function is DateDiff(Interval, Date1, Date2, Option1, Option2) Required, the Interval argument specifies what kind of value you want as a result. This argument will be enclosed between double quotes and can have one of the following values:
Required also, the Date1 and Date2 arguments specify the date or time values that will be used when performing the operation. By default, the days of a week are counted starting on Sunday. If you want to start counting those days on another day, pass the Option1 argument using one of the following constants: vbSunday, vbMonday, vbTuesday, vbWednesday, vbThursday, vbFriday, vbSaturday. There are other variances to that argument. If your calculation involves weeks or finding the number of weeks, by default, the weeks are counted starting January 1st. If you want to count your weeks starting at a different date, use the Option2 argument to specify where the program should start.
Besides the libraries used in Microsoft Access, the Microsoft Windows operating system provides its own library of functions and objects. This library is called the Win32 Application Programming Interface or Win32 API, or simply Win32. The Win32 library is somehow available to applications but its functions are not directly available for a database. The Win32 library is made of procedures, functions, and classes (mostly structures) that you can use to complement a project. There are so many of these functions and objects that it is hard to know most or all of them. The best way to get acquainted with them is to check its documentation. To do this, you can visit the MSDN web site. The functions are stored in various sublibraries called dynamic link libraries (DLLs).
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" (Arguments) As DataType The Win32FunctionName factor is the name of the function in the Win32 library. The LibraryName is the name of the library. You can create a custom name for the function as the CustomName factor. In the parentheses, you can enter the names and types of the arguments. If the procedure returns a value, you can specify its type after the As keyword.



Previous  Copyright © Yevol, 2007  Next 
