Practical
Learning: Introducing BuiltIn Functions


 Start Microsoft Access and create a blank database named
Solas Property Management
 To create a new table, on the main menu, click Insert > Table
 In the New Table dialog box, doubleclick Table Wizard
 In the Sample Tables list, click Invoices
 In the Sample Fields list, doubleclick InvoiceID and InvoiceDate
 Click Next
 Accept the Name of the table as Invoices and click Next
 Click the Modify the Table Design radio button and click Finish
 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 


 Save and close the table
 Using AutoForm, generate a form for the Invoices table and design
it as follows:
 Save the form as Invoices
 Switch it to Form View to preview
 Switch it back to Design View
 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 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.


Practical
Learning: Using the Eval() Function


 On the form, click the text box under the Quantity label
 In the Events tab of the Properties window, doubleclick On Lost Focus,
then click its ellipses button and implement the event as follows:
Private Sub Item1Quantity_LostFocus()
[Item1SubTotal] = Eval([Item1UnitPrice] * [Item1Quantity])
End Sub

 Return to Microsoft Access and, on the form, click the second text box
under Quantity
 In the Events tab of the Properties window, doubleclick On Lost Focus and
click its ellipsis button to implement the event as follows:
Private Sub Item2Quantity_LostFocus()
[Item2SubTotal] = Eval([Item2UnitPrice] * [Item2Quantity])
End Sub

 Return to Microsoft Access and, on the form, click the third text box
under Quantity
 In the Events tab of the Properties window, doubleclick On Lost Focus and
click its ellipsis button to implement the event as follows:
Private Sub Item3Quantity_LostFocus()
[Item3SubTotal] = Eval([Item3UnitPrice] * [Item3Quantity])
End Sub

 Return to Microsoft Access and, on the form, click the fourth text box
under Quantity
 In the Events tab of the Properties window, doubleclick On Lost Focus and
click its ellipsis button to implement the event as follows:
Private Sub Item4Quantity_LostFocus()
[Item4SubTotal] = Eval([Item4UnitPrice] * [Item4Quantity])
End Sub

 Return to Microsoft Access and, on the form, click the fifth text box
under Quantity
 In the Events tab of the Properties window, doubleclick On Lost Focus and
click its ellipsis button to implement the event as follows:
Private Sub Item5Quantity_LostFocus()
[Item5SubTotal] = Eval([Item5UnitPrice] * [Item5Quantity])
End Sub

 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
Learning: Using the Val() Function


 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

 Return to the form and save it
 Rightclick the form and click Form Header/Footer
 From the Toolbox, click Command Button and click under the Form Footer bar
 Using the wizard, create a button that would be used to Close the Form and
name it cmdClose
 Create a few invoices
 Close the Invoices 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:
Format(Expression)


