Expressions and Functions in Numeric Values

Characteristics of Number-Based Fields and Numeric Values

Conversions

Microsoft Access makes it particular easy to convert a value from one type to another. If you primarily create a Short Text-based field but that only contains numbers, you can just change its data type and set the appropriate format. You may receive a warning that some values will be lost but the conversion should be smooth. In the same way, you can convert numeric values from one type (such as Integer) to another type (such as Double).

When making this type of conversion, make sure the original values can "fit" in the new type. For example, you shouldn't try to change the type a column that has large integral values to a Byte.

Number-Based Columns and Expressions

As seen for Short Text-based columns, in a table, you can create a field whose type is an expression.

Practical Learning: Creating Expression-Based Columns

1. Start Microsoft Access
2. In the list of files, click FunDS1 from the previous lesson
3. In the Navigation Pane, right-click the StoreItems form and click Design View
4. On the form, click the text box below the Amount label
5. In the Property Sheet, click the Data tab, click Control Source and click its ellipsis button
6. In the bottom middle list of the Expression Builder, double-click UnitPrice
7. Type *
8. In the bottom middle list, double-click txtDiscountRate

   

9. Click OK
10. On the form, click the text box below After Discount
11. In the Data tab of the Property Sheet, click Control Source and click its ellipsis button
12. In the bottom middle list of the Expression Builder, double-click UnitPrice
13. Type -
14. In the bottom middle list, double-click txtDiscountAmount

    

15. Click OK
16. Close the form
17. When asked whether you want to save, click Yes
18. In the Navigation Pane, double-click the SoldItems table and create the following records:
     

    Receipt #	Item #	Manufacturer	Category	Sub-Category	Item Name	Item Size	Purchase Price
    100001	211771	Ralph Lauren	Women	Dresses	Petite Knit Tweed Short S	12	39.88
    100001	290699	Ralph Lauren	Women	Shoes	Whip Snake Print Perfect	6.5	148.50
    100002	136843	AK Anne Klein	Women	Watches	Ladies White Ceramic Crys		126.75
    100003	345500	Calvin Klein	Women	Skirts	Petite Crossing Paths Pri	12	60
    100003	388729	AK Anne Klein	Women	Shoes	Claw Buckle-Detail Wedge	1	69.95
    100003	239679	Lauren by Ralph Lauren	Women	Dresses	Sleeveless Shimmer Dress	6	75
    100003	388663	Lauren by Ralph Lauren	Women	Belts	Braided Leather Belt	Medium	54.25
    100004	379367	Calvin Klein	Men	Shoes	Hervey Square-Toe Leather	11	90
    100005	426643	Calvin Klein	Men	Accessories	Knit Scarf		37.50
    100005	430669	Kenneth Cole New York	Women	Jewelry	Goldtone Beaded Double-Dr		26.95
    100006	441180	Ralph Lauren	Girls	Shirts	Girls 2-6X Short-Sleeved	3/3T	35
    100006	668156	Ralph Lauren	Girls	Dresses	Girls 2-6x Casey Cotton T	4/4T	40
    100006	366154	Coach	Girls	Dresses	Girls 2-6X Cotton Tiered	6X	37.40
    100006	746827		Women		Oyster Long Satin Nightgo		58.40
    100006	735312	Cole Haan	Women	Watches	Ladies Black Python-Embos		48
    100006	545127	Calvin Klein	Men	Shoes	Suede Desert Boots	9.5	58.30

19. Close the form
20. When asked whether you want to save the form, click Yes
21. In the Navigation Pane, double-click the ShoppingSessions table
22. Update some records as follows:
     

    Receipt #	Amount Tendered
    100001	200
    100002	130
    100003	200
    100004	90
    100005	100
    100006	300

Introduction to Number-Based Built-In Functions

The Absolute Value

The absolute value of a number x is x if the number is (already) positive. If the number is negative, then its absolute value is its positive equivalent. For example, the absolute value of 12 is 12, while the absolute value of -12 is 12.

To get the absolute value of a number, you can use one of the Abs() function. Its syntax is:

Abs(number)

The Exponential

The Exp() function is used to calculate the exponential value of a number. Its syntax is:

EXP(number)

The argument, number, a double-precision value, represents the number to be evaluated. If the value of number is less than -708.395996093 (approximately), the result is reset to 0 and qualifies as underflow. If the value of the argument x is greater than 709.78222656 (approximately), the result is infinity and qualified as overflow

The Square Root

The Sqr() function is used to calculate the square root of a double-precision number. Its syntax is:

Sqr(number)

This function takes one argument as a positive floating number. After the calculation, the function returns the square root of x.

Series-Based Functions

Introduction

A series or collection-based function is one that considers a particular column and performs an operation on all of its cells. For example, if you have a particular column in which users enter a string, you may want to count the number of strings that have been entered in the cells of that column. In the same way, suppose you have a column under whose cells users must enter numbers. Using a series-based function, you can get the total of the values entered in the cells of that column.

The general syntax of series-based functions is:

function-name(Series)

The function-name is one of those we will see shortly. Each of these functions takes one argument, which is usually the name of the column whose cells you want to consider for the operation.

The Number of Records

Count: The Count() function is used to count the number of values entered in a column.

Practical Learning: Getting the Number of Records

1. The FunDS1 database should still be opened.
   In the Navigation Pane, right-click the StoreItems form and click Design View
2. In the Controls section of the Ribbon, click the Text Box and Click in the Form Footer section
3. Set the caption of its accompanying label as Number of Store Items:
4. Double-click the text box on the form
5. In the Property Sheet, click the All tab and change the properties as follows:
   Name: txtNumberOfStoreItems
   Control Source: =Count([ItemNumber])
6. Right-click the title bar of the form and click Form View
7. Close the form
8. When asked whether you want to save, click Yes

The Sum of Values

To perform the addition on various values of a column, call the Sum() function.

Practical Learning: Getting the Number of Records

1. In the Navigation Pane, right-click sfSoldItems and click Design View
2. In the Controls section of the Ribbon, click the Text Box and Click in the Form Footer section
3. As the text box is selected on the form, in the Property Sheet, click the All tab and change the following properties:
   Name: txtSumOfPurchasePrices
   Control Source: =Sum([PurchasePrice])
4. Right-click the title bar of the form and click Form View
5. Close the form
6. When asked whether you want to save, click Yes

Sub-Form/Sub-Report Expressions and the Parent Object

If you add a sub-form (or sub-report) to a form (or report) and that sub-form (or sub-report) contains a control that includes an expression, you may want to access the result of that expression in the hosting form (or report). In this case, you have a transitional relationship where the control is a child of the sub-form or sub-report. The sub-form or sub-report is a child of the hosting form or report.

From a form, the basic formula to access a control positioned in a sub-form is:

sub-form name.Form!control name

An alternative is to start with the Forms collection. The formula would be:

Forms!form-name!sub-form-name.Form.control-name

From a report, the basic formula to access a control positioned in a sub-report is:

sub-report name.Report!control name

The name of the sub-form or sub-report and the name of the control can (should) be written inside square brackets. If you know the expression you want to use, you can type it manually.

Otherwise, you can use the Expression Builder to reduce mistakes.

Practical Learning: Accessing a Control on a Sub-Form

1. In the Navigation Pane, right-click the ShoppingSessions form and click Design View
2. In the Controls section of the Ribbon, click the Text Box and click below the sub-form on the form
3. As the text box is selected on the form, in the Property Sheet, click the All tab, change the following characteristics:
   Name: txtSalesTotal
   Format: Standard
4. Click Control Source and click its browse button
5. In the bottom left list of the Expression Builder, click the + button of ShoppingSessions and click sfSoldItems
6. In the bottom middle list of the Expression Builder, double-click txtSumOfPurchasePrices

   

7. Click OK
8. In the Tools section of the Ribbon, click Add Existing Fields
9. In the Field List, drag AmountTendered and drop it in the Detail section of the form
10. In the Controls section of the Ribbon, click the Text Box and click in the Detail section of the form
11. Right-click the text box on the form and click Properties
12. In the All tab of the Property Sheet, change the following characteristics:
    Name: txtChange
    Format: Standard
13. Click Control Source and click its browse button
14. In the bottom-middle list box, double-click AmountTendered
15. Type -
16. In the bottom-middle list, double-click txtSalesTotal

    

17. Click OK
18. Design the form as follows:

    

19. To preview, right-click the title bar of the form and click Form View

    

20. Close the form
21. When asked whether you want to save, click Yes
22. In the Navigation Pane, right-click sfSoldItems and click Design View
23. Double-click the Form Footer section
24. In the Property Sheet, the Format tab and double-click Visible to set its value to No
25. Close the sub-form
26. When asked whether you want to save, click Yes
27. In the Navigation Pane, double-click the ShoppingSessions form

    

    

    

28. Close the form
29. On the Ribbon, click File and click Open
30. In the list of files, click College Park Auto-Repair1 from the previous lesson
31. In the Navigation Pane, right-click JobsPerformed and click Design View
32. Click the first empty box under Field Name
33. Type Cost, press Tab and type n (make sure number was selected)
34. In the lower section of the window, change the following characteristics:
    Field Size: Double
    Format: Fixed
35. Save and close the table
36. In the Navigation Pane, right-click sfJobsPerformed and click Design View
37. In the Tools section of the Ribbon, click Add Existing Fields
38. From the Field List, drag Cost and drop it in the Detail section of the form
39. Move its label to the Form Header section (you can right-click it and click Cut, then right-click the Form Header and click Paste) then move both the label and the text box to the right
40. Format the label and the text box to use the same font and border color as the other controls in the same section:

    

41. Save and close the sub-form
42. In the Navigation Pane, right-click PartsUsed and click Design View
43. Click the first empty box under Field Name
44. Type UnitPrice and set its data type to Number
45. In the lower section of the window, change the following characteristics:
    Field Size: Double
    Format: Fixed
    Caption: Unit Price
46. Click the first empty box under Field Name
47. Type Quantity and set its data type to Number
48. In the lower section of the window, its Field Size to Byte
49. Save and close the table
50. In the Navigation Pane, right-click sfPartsUsed and click Design View
51. In the Tools section of the Ribbon, click Add Existing Fields
52. From the Field List, drag UnitPrice and drop it in the Detail section of the form
53. Move its label to the Form Header section then move both the label and the text box to the right
54. From the Field List, drag Quantity and drop it in the Detail section of the form
55. Move its label to the Form Header section and change its caption to Qty
56. Move both the label and the text box to the right

    

57. Save and close the sub-form
58. In the Navigation Pane, right-click the RepairOrders table and click Design View
59. In the top portion of the window, right-click ProblemDescription and click Insert Rows
60. Type CarYear and set its data type as Number
61. In the lower section of the window, change the following characteristics:
    Field Size: Integer
    Caption: Year
62. In the top portion of the window, right-click Recommendations and click Insert Rows
63. Type TaxRate and set its data type as Number
64. In the lower section of the window, change the following characteristics:
    Field Size: Double
    Format: Percent
    Caption: Tax Rate
65. Save and close the table
66. In the Navigation Pane, right-click the RepairOrders form and click Design View
67. In the Tools section of the Ribbon, click Add Existing Fields
68. From the Field List, drag CarYear and drop it to the right of the Model text
69. From the Field List, drag TaxRate and drop it below the sub-form
70. Format the controls to appear like the others:

    

71. Save and close the form

Business Functions

Introduction

An asset is an object of value. It could be a person, a car, a piece of jewelry, a refrigerator. Anything that has a value is an asset. In the accounting world, an asset is a piece of/or property whose life's span can be projected, estimated, or evaluated. As days, months or years go by, the value of such an asset degrades.

When an item is acquired for the first time as "brand new", the value of the asset is referred to as its cost. The declining value of an asset is referred to as its depreciation. At one time, the item will completely lose its worth or productive value. Nevertheless, the value that an asset has after it has lost all of its value is referred to its salvage value. At any time, between the purchase value and the salvage value, accountants estimate the value of an item based on various factors including its original value, its lifetime, its usefulness (how the item is being used), etc.

The Double Declining Balance

The Double Declining Balance is a method used to calculate the depreciating value of an asset. To get it, you can use the DDB function whose syntax is:

DDB(cost, salvage, life, period)

The first argument, cost, represents the initial value of the item.

The salvage argument is the estimated value of the asset when it will have lost all its productive value. The cost and the salvage values must be given in their monetary values.

The value of life is the length of the lifetime of the item; this could be the number of months for a car or the number of years for a house, for example.

The period is a factor for which the depreciation is calculated. It must be in the same unit as the life argument. For the Double Declining Balance, this period argument is usually 2.

The Straight Line Method

Another method used to calculate the depreciation of an item is through a concept referred to as the Straight Line Method. This time, the depreciation is considered on one period of the life of the item. The function used is SLN and its syntax is:

SLN(cost, salvage, life);

The cost argument is the original amount paid for an item (refrigerator, mechanics toolbox, high-volume printer, etc).

The salvage, also called the scrap value, is the value that the item will have (or is having) at the end of Life.

The life argument represents the period during which the asset is (or was) useful; it is usually measured in years.

The Sum of the Years' Digits

The Sum-Of-The-Years-Digits provides another method for calculating the depreciation of an item. Imagine that a restaurant bought a commercial refrigerator ("cold chamber") for $18,000 and wants to estimate its depreciation after 5 years using the Sum-Of-Years-Digits method. Each year is assigned a number, also called a tag, using a consecutive count; this means that the first year is appended 1, the second is 2, etc. This way, the depreciation is not uniformly applied to all years.

Year => 1, 2, 3, 4, and 5

The total count is made for these tags. For our refrigerator example, this would be

Sum = 1 + 2 + 3 + 4 + 5 = 15

Each year is divided by this sum, also called the sum of years, used as the common denominator:

This is equivalent to 1. As you can see, the first year would have the lowest dividend (1/15 ≈ 0.0067) and the last year would have the highest (5/15 ≈ 0.33).

To calculate the depreciation for each year, the fractions (1/15 + 2/15 + 3/15 + 4/15 + 5/15) are reversed so that the depreciation of the first year is calculated based on the last fraction (the last year divided by the common denominator). Then the new fraction for each year is multiplied by the original price of the asset. This would produce (this table assumes that the refrigerator will have a value of $0.00 after 5 years):

The function used to calculate the depreciation of an asset using the sum of the years' digits is called SYD and its syntax is:

SYD(cost, salvage, life, period)

The cost argument is the original value of the item; in our example, this would be $18,000.

The salvage parameter is the value the asset would have (or has) at the end of its useful life.

The life is the number of years the asset would have a useful life (because assets are usually evaluated in terms of years instead of months).

The period parameter is the particular period or rank of a Life portion. For example, if the life of the depreciation is set to 5 (years), the period could be any number between 1 and 5. If set to 1, the depreciation would be calculated for the first year. If the Period is set to 4, the depreciation would calculated for the 4th year. You can also set the period to a value higher than life. For example, if life is set to 5 but you pass 8 for the period, the depreciation would be calculated for the 8th year. If the asset is worthless in the 8th year, the depreciation would be 0.

Finance Functions

Introduction

Microsoft Access provides a series of functions destined to perform various types of financially related operations. These functions use common factors depending on the value that is being calculated. Many of these functions deal with investments or loan financing.

The Present Value is the current value of an investment or a loan. For a savings account, a customer could pledge to make a set amount of deposit on a bank account every month. The initial value that the customer deposits or has in the account is the Present Value. The sign of the variable, when passed to a function, depends on the position of the customer. If the customer is making deposits (car loan, boat financing, etc), this value must be negative. If the customer is receiving money (lottery installment, family inheritance, etc), this value should be positive.

The Future Value is the value the loan or investment will have when the loan is paid off or when the investment is over. For a car loan, a musical instrument loan, a financed refrigerator, a boat, etc, this is usually 0 because the company that is lending the money will not take that item back (they didn't give it to the customer in the first place, they only lend him or her some money to buy the item). This means that at the end of the loan, the item (such as a car, boat, guitar, etc) belongs to the customer and it is most likely still worth something.

As described above and in reality, the Future Value is the amount the item would be worth at the end. In most, if not all, loans, it would be 0. On the other hand, if a customer is borrowing money to buy something like a car, a boat, a piano, etc, the salesperson would ask if the customer wants to put a "down payment", which is an advance of money. Then, the salesperson or loan officer can either use that down payment as the Future Value parameter or simply subtract it from the Present Value and then apply the calculation to the difference. Therefore, you can apply some type of down payment to your functions as the Future Value.

The Number Of Periods is the number of payments that make up a full cycle of a loan or an investment.

The Interest Rate is a fixed percent value applied during the life of the loan or the investment. The rate does not change during the length of the Periods.

For deposits made in a savings account, because their payments are made monthly, the rate is divided by the number of periods (the Periods) of a year, which is 12. If an investment has an interest rate set at 14.50%, the Rate would be 14.50/12 = 1.208. Because the Rate is a percentage value, its actual value must be divided by 100 before passing it to the function. For a loan of 14.50% interest rate, this would be 14.50/12 = 1.208/100 = 0.012.

The Payment is the amount the customer will be paying. For a savings account where a customer has pledged to pay a certain amount in order to save a set (goal) amount, this would be the amount the customer would pay every month. If the customer is making payments (car loan, mortgage, deposits to a savings account, etc), this value must be negative. If the customer is receiving money (lottery installment or annuity, family inheritance, etc), this value must be positive.

The Payment Time specifies whether the payment is made at the beginning or the end of the period. For a monthly payment, this could be the beginning or end of every month.

The Future Value of an Investment

To calculate the future value of an investment, you can use the FV() function. The syntax of this function is:

FV(Rate, Periods, Payment, PresentValue, PaymentType)

The Number of Periods of an Investment

To calculate the number of periods of an investment or a loan, you can use the NPer() function. Its syntax is:

NPer(Rate, Payment, PresentValue, FutureValue, PaymentType);

Investment or Loan Payment

The Pmt() function is used to calculate the regular payment of loan or an investment. Its syntax is:

Pmt(Rate, NPeriods, PresentValue, FutureValue, PaymentType)

In the following example, a customer is applying for a car loan. The cost of the car will be entered in cell C4. It will be financed at a rate entered in cell C6 for a period set in cell C7. The dealer estimates that the car will have a value of $0.00 when it is paid off.

The Amount Paid As Interest During a Period

When a customer is applying for a loan, an investment company must be very interested to know how much money it would collect as interest. This allows the company to know whether the loan is worth giving. Because the interest earned is related to the interest rate, a company can play with the rate (and also the length) of the loan to get a fair (?) amount.

The IPmt() function is used to calculate the amount paid as interest on a loan during a period of the lifetime of a loan or an investment. It is important to understand what this function calculates. Suppose a customer is applying for a car loan and the salesperson decides (or agrees with the customer) that the loan will be spread over 5 years (5 years * 12 months each = 60 months). The salesperson then applies a certain interest rate. The IPMT() function can help you calculate the amount of interest that the lending institution would earn during a certain period. For example, you can use it to know how much money the company would earn in the 3rd year, or in the 4th year, or in the 1st year. Based on this, this function has an argument called Period, which specifies the year you want to find out the interest earned in.

The syntax of the IPmt() function is:

IPmt(Rate, 
    Period, NPeriods, PresentValue, FutureValue, PaymentType)

The Rate argument is a fixed percent value applied during the life of the loan.

The PresentValue is the current value of the loan or investment. It could be the marked value of the car, the current mortgage value of a house, or the cash amount that a bank is lending.

The FutureValue is the value the loan or investment will have when the loan is paid off.

The NPeriods is the number of periods that occur during the lifetime of the loan. For example, if a car is financed in 5 years, this value would be (5 years * 12 months each =) 60 months. When passing this argument, you must remember to pass the right amount.

The Period argument represents the payment period. For example, it could be 3 to represent the 3rd year of a 5 year loan. In this case, the IPmt() function would calculate the interest earned in the 3rd year only.

The PaymentType specifies whether the periodic (such as monthly) payment of the loan is made at the beginning (1) or at the end (1) of the period.

The FutureValue and the PaymentType arguments are not required.

The Amount Paid as Principal

While the IPmt() function calculates the amount paid as interest for a period of a loan or an investment, the PPmt() function calculates the actual amount that applies to the balance of the loan. This is referred to as the principal. Its syntax is:

PPMT(Rate, Period, NPeriods, 
      PresentValue, FutureValue, PaymentType)

The argument are the same as described in the previous sections

The Present Value of a Loan or an Investment

The PV() function calculates the total amount that future investments are worth currently. Its syntax is:

PV(Rate, NPeriods, Payment, FutureValue, PaymentType)

The arguments are the same as described earlier.

The Interest Rate

Suppose a customer comes to your car dealer and wants to buy a car. The salesperson would first present the available cars to the customer so the customer can decide what car he likes. After this process and during the evaluation, the sales person may tell the customer that the monthly payments would be $384.48. The customer may then say, "Wooooh, I can't afford that, man". Then the salesperson would ask, "What type of monthly payment suits you". From now on, both would continue the discussion. Since the salesperson still wants to make some money but without losing the customer because of a high monthly payment, the salesperson would need to find a reasonable rate that can accommodate an affordable monthly payment for the customer.

The Rate() function is used to calculate the interest applied on a loan or an investment. Its syntax is:

RateE(NPeriods, Payment, PresentValue, FutureValue, PaymentType, Guess)

All of the arguments are the same as described for the other functions, except for the Guess. This argument allows you to give some type of guess for a rate. This argument is not required. If you omit it, its value is assumed to be 10.

The Internal Rate of Return

The IRR() function is used to calculate an internal rate of return based on a series of investments. Its syntax is:

IRR(Values, Guess)

The Values argument is a series (also called an array or a collection) of cash amounts that a customer has made on an investment. For example, a customer could make monthly deposits in a savings or credit union account. Another customer could be running a business and receiving different amounts of money as the business is flowing (or losing money). The cash flows don't have to be the same at different intervals but they should (or must) occur at regular intervals such as weekly (amount cut from a paycheck), bi-weekly (401k directly cut from paycheck, monthly (regular investment), or yearly (income). The Values argument must be passed as a collection of values, such as a range of selected cells, and not an amount. Otherwise you would receive an error.

The Guess parameter is an estimate interest rate of return of the investment.

The Net Present Value

The NPV() function uses a series of cash flows to calculate the present value of an investment. Its syntax is:

NPV(Rate, Value1, Value2, ...)

The Rate parameter is the rate of discount in during one period of the investment.

As the NPV() function doesn't take a fixed number of arguments, you can add a series of values as Value1, Value2, etc. These are regularly made payments for each period involved. Because this function uses a series of payments, any payment made in the past should have a positive value (because it was made already). Any future payment should have a negative value (because it has not been made yet).

Practical Learning: Ending the Lesson

• Close Microsoft Access