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 and save it
  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. Close the Invoices 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:

Format(Expression)

 

 
 
Previous Copyright © 2002-2005 FunctionX, Inc. Next