Introduction to Built-In Functions

Accessory Functions and Procedures

In our introduction to procedures in Lesson 5, we saw different ways or creating modules, sub-procedures 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.

1. Start Microsoft Access and create a blank database named
   Solas Property Management
2. To create a new table, on the main menu, click Insert -> Table
3. In the New Table dialog box, double-click Table Wizard
4. In the Sample Tables list, click Invoices
5. In the Sample Fields list, double-click InvoiceID and InvoiceDate
6. Click Next
7. Accept the Name of the table as Invoices and click Next
8. Click the Modify the Table Design radio button and click Finish
9. Add the following fields to the table:
    

   Field Name	Data Type	Field Size	Format
   InvoiceID
   InvoiceDate
   ContractorName
   ContractorPhoneNumber
   ContractorAddress		80
   ContractorCity
   ContractorState
   ContractorZIPCode
   LaborAddress		80
   LaborCity
   LaborState
   LaborZIPCode
   Labor1		80
   Labor2		80
   Labor3		80
   Labor4		80
   Labor5		80
   Item1Name
   Item1UnitPrice	Number	Double	Fixed
   Item1Quantity	Number	Byte
   Item1SubTotal	Number	Double	Fixed
   Item2Name
   Item2UnitPrice	Number	Double	Fixed
   Item2Quantity	Number	Byte
   Item2SubTotal	Number	Double	Fixed
   Item3Name
   Item3UnitPrice	Number	Double	Fixed
   Item3Quantity	Number	Byte
   Item3SubTotal	Number	Double	Fixed
   Item4Name
   Item4UnitPrice	Number	Double	Fixed
   Item4Quantity	Number	Byte
   Item4SubTotal	Number	Double	Fixed
   Item5Name
   Item5UnitPrice	Number	Double	Fixed
   Item5Quantity	Number	Byte
   Item5SubTotal	Number	Double	Fixed
   TotalLabor	Number	Double	Fixed
   TotalItems	Number	Double	Fixed
   Notes	Memo

10. Save and close the table
11. Using AutoForm, generate a form for the Invoices table and design it as follows:
     
    
12. Save the form as Invoices
13. Switch it to Form View to preview
     
    
14. Switch it back to Design View
15. Save the form

To assist you with evaluating an expression, Microsoft Access 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.

1. On the form, click the text box under the Quantity label
2. In the Events tab of the Properties window, double-click On Lost Focus, then click its ellipses button and implement the event as follows:
    

   Private Sub Item1Quantity_LostFocus() [Item1SubTotal] = Eval([Item1UnitPrice] * [Item1Quantity]) End Sub

3. Return to Microsoft Access and, on the form, click the second text box under Quantity
4. In the Events tab of the Properties window, double-click On Lost Focus and click its ellipsis button to implement the event as follows:
    

   Private Sub Item2Quantity_LostFocus() [Item2SubTotal] = Eval([Item2UnitPrice] * [Item2Quantity]) End Sub

5. Return to Microsoft Access and, on the form, click the third text box under Quantity
6. In the Events tab of the Properties window, double-click On Lost Focus and click its ellipsis button to implement the event as follows:
    

   Private Sub Item3Quantity_LostFocus() [Item3SubTotal] = Eval([Item3UnitPrice] * [Item3Quantity]) End Sub

7. Return to Microsoft Access and, on the form, click the fourth text box under Quantity
8. In the Events tab of the Properties window, double-click On Lost Focus and click its ellipsis button to implement the event as follows:
    

   Private Sub Item4Quantity_LostFocus() [Item4SubTotal] = Eval([Item4UnitPrice] * [Item4Quantity]) End Sub

9. Return to Microsoft Access and, on the form, click the fifth text box under Quantity
10. In the Events tab of the Properties window, double-click On Lost Focus and click its ellipsis button to implement the event as follows:
     

    Private Sub Item5Quantity_LostFocus() [Item5SubTotal] = Eval([Item5UnitPrice] * [Item5Quantity]) End Sub

11. Return to the form and save it

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:

1. Return to Microsoft Visual Basic and change the LostFocus events as follows:
    

   Private Sub Item1Quantity_LostFocus() [Item1SubTotal] = Eval([Item1UnitPrice] * [Item1Quantity]) [TotalItems] = Val(Nz([Item1SubTotal]) + Nz([Item2SubTotal]) + _ Nz([Item3SubTotal]) + Nz([Item4SubTotal]) + _ Nz([Item5SubTotal])) End Sub Private Sub Item2Quantity_LostFocus() [Item2SubTotal] = Eval([Item2UnitPrice] * [Item2Quantity]) [TotalItems] = Val(Nz([Item1SubTotal]) + Nz([Item2SubTotal]) + _ Nz([Item3SubTotal]) + Nz([Item4SubTotal]) + _ Nz([Item5SubTotal])) End Sub Private Sub Item3Quantity_LostFocus() [Item3SubTotal] = Eval([Item3UnitPrice] * [Item3Quantity]) [TotalItems] = Val(Nz([Item1SubTotal]) + Nz([Item2SubTotal]) + _ Nz([Item3SubTotal]) + Nz([Item4SubTotal]) + _ Nz([Item5SubTotal])) End Sub Private Sub Item4Quantity_LostFocus() [Item4SubTotal] = Eval([Item4UnitPrice] * [Item4Quantity]) [TotalItems] = Val(Nz([Item1SubTotal]) + Nz([Item2SubTotal]) + _ Nz([Item3SubTotal]) + Nz([Item4SubTotal]) + _ Nz([Item5SubTotal])) End Sub Private Sub Item5Quantity_LostFocus() [Item5SubTotal] = Eval([Item5UnitPrice] * [Item5Quantity]) [TotalItems] = Val(Nz([Item1SubTotal]) + Nz([Item2SubTotal]) + _ Nz([Item3SubTotal]) + Nz([Item4SubTotal]) + _ Nz([Item5SubTotal])) End Sub

2. Return to the form
3. Right-click the form and click Form Header/Footer
4. From the Toolbox, click Command Button and click under the Form Footer bar
5. Using the wizard, create a button that would be used to Close the Form and name it cmdClose 
6. Create a few invoices
7. Save and close the form

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 double-precision. 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 arithmetic-oriented 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.

1. In the Database window, click Forms.
   Right-click Invoices and click Save As...
2. Enter InvoicesSummaries as the name of the form and click OK
3. Right-click InvoicesSummaries and click Design View
4. Change the design of the form as follows:
    

   Control Caption Name Control Source Option Group Summary of Invoices     Text Box Nbr of Invoices txtNumberOfInvoices =Count([InvoiceID]) Text Box Labor Expenses txtLaborExpenses =Sum([TotalLabor]) Text Box Average txtAverageLabor =Avg([TotalLabor]) Text Box Parts Expenses txtPartsExpenses =Sum([TotalItems]) Text Box Average txtAverageExpenses =Avg([TotalItems])

5. Preview the form
    
   
6. Save and close the form

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.

1. On the main menu of Microsoft Access, click Tools -> Macro Visual Basic Editor

2. On the main menu of Microsoft Visual Basic, click View -> Immediate Window

3. In the Immediate window, type ?Date and press Enter

4. Notice that the system date is displayed

5. Still in the Immediate window, type ?Time and press Enter

6. Again, in the Immediate window, type ?Now and press Enter

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)

1. In the Immediate window, type ?Day(#12/14/00#) and press Enter
2. Notice that the result is 14 as the numeral day of that date
3. Still in the Immediate window, type ?Day(Date()) and press Enter
4. Notice that the result is the numeric day of the system date
5. In the Immediate window, type ?Day(Date()+2) and press Enter
   This would display the numeral of 2 days from today
6. In the Immediate window, type ?Month("June 12, 1990") and press Enter
7. Notice that the result in 6, because June represents the 6th month of the year
8. In the Immediate window, type ?Month(#02/25/90#) and press Enter
   This produces 2 since February is the 2nd day of the year
9. In the Immediate window, type ?Year(Date()) and press Enter
10. Notice that this displays the current year of the system date.

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 sub-libraries 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.

1. Open the Department of Records and Statistics database you created in the first lesson 
2. In the Database window, click the Forms button and double-click DRS
3. Switch the form to Design View
4. Right-click the Create Database button and click Build Event...
5. In the Choose Builder dialog box, click Code Builder and click OK
6. Change the file as follows:
    

   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

7. Return to Microsoft Access and switch the form to Form View
8. Click the Create Database button and switch the form to Design View
9. Open a My Documents window and notice that a database named Motor Vehicle Division has been created
10. Close Microsoft Access