Home

Introduction to Built-In Functions

 

Accessory Functions and Procedures

 

Introduction

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.

 

Practical LearningPractical Learning: Introducing Built-In Functions

  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

Expression Evaluation

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.

 

Practical LearningPractical Learning: Using the Eval() Function

  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

The Numeric Value of an Expression

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:

Practical LearningPractical Learning: Using the Val() Function

  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

Value Formatting

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.

Statistical Functions

 

Introduction

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.

Using Statistical Functions

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.

Practical Learning: Using Statistical Functions

  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

Date and Time-Based Functions

 

Now - Date - Time

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.

 

Practical Learning: Getting the System Date and Time

  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

Day - Month - Year

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)

Practical Learning: Getting the Day, Month, and Year Values of a Date

  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.

Operations on Date and Time Values

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:

Interval Used To Get
s Second
n Minute
h Hour
w Numeric Weekday
ww Week of the Year
d Day
y Numeric Day of the Year
m Month
q Quarter
yyyy Year

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.

Date Difference

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:

Interval Used to Get
s Second
n Minute
h Hour
w Numeric Weekday
ww Week of the Year
d Day
y Numeric Day of the Year
m Month
q Quarter
yyyy Year

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.

The Win32 API

 

Introduction

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).

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" (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.

Practical LearningPractical Learning: Using the Win32 API

  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

 

 

Previous Copyright Yevol, 2007 Next