Tables & Queries:
Calculated Values - Numbers

Step-by-Step: Calculated Values - Numbers

---

What you will learn:

to add currency field to enter character not on keyboard to create a calculated number field to format a calculated value to calculate a value with IIf to use nested IIf statements

---

Start with:  , Projects database open.

Add Fields

The tables you have in the Projects database so far do not have much in the way of number data to do calculations with. You need to add some number fields and data.

1. Open the table Projects in Table Design View.
    
2. Add a new field to the list named Cost.
    
3. Select the data type Number and change the Format property to Currency.
    
   The currency formatting will match the default currency from the Regional and Language Options dialog in the Control Panel. If you choose English - American, you see $ as the currency symbol and a period before the decimal part of a number. If you choose Spanish - Paraguay, you see Gs as the currency symbol and a comma before the decimal portion of the value. You must know what country you are in!
   
   The default value for a number field is zero. You won't have a null value for this type of field.
   
    
4. Add another field named Budget with the same characteristics.
    
5. Save the table and switch to Table Datasheet View.
    

---

Enter Values: Character not on keyboard

Modern fonts have a LOT more characters than are on the keyboard. Many languages use characters that are not in English. For example Spanish includes many accented characters like á, é, ú, Õ and ñ. French includes characters like è and ç. You can buy a keyboard that is customized for your own language.

Characters that are not on your keyboard can be entered with a combo of ALT + keypad numbers. But, how do you know what the number is for your special character??

Symbol Font: In Word and Excel you have a menu command,  Insert | Symbol..., that lets you see all of the characters in a font and use a handy Insert button. The bottom of the Symbol window shows the number to use with the ALT key. Unhappily, Access does not have this command.
 

Character Map: You can open another feature, Start menu | Run | type charmap into the box and click OK. The Character Map window opens. Click on a character and its key combo shows at the bottom right. The illustration is from WinXP Pro.

Alternate keyboard layout: If you work a lot in a second language, you can install an alternate keyboard layout in Regional and Language Options in the Control Panel, and then switch back and forth between layouts. Of course, your physical keyboard won't change so you must be familiar with what characters are where.

Or you can make a cheat sheet of the numbers like I did for Spanish characters to keep handy when I am editing the Spanish version of one of my lessons.

 

1. Enter the following values into the Cost and Budget fields:

   Project 1:    433     450
   Project 2:  1500    1500
   Project 3:  2348    2000
   The table automatically formats these as currency.
    

2. Add the following additional records:
    

   ProjectName	ProjectDescription	Cost	Budget
   Late Winter Sale	Promoting travel to warm climates at end of winter season	$225.00	$250.00
   Recruitment for Australia	Campaign to hire agents and other staff for the new Australia regional office	$5,125.00	$5,000.00
   BMA Medical Conference	Meeting planning/setup for medical conference in São Paulo, Brazil		$7,500.00
   Networking Upgrade	Researching choices for systems and vendors for a new network		$100.00

   Problem: How to type characters not on the keyboard
   To type ã in the medical conference record (for São Paulo), hold the ALT key down and on the numeric keypad press 0227.  Now you know one code!

   

3. Close the table.

---

Create Calculated Number Value

Now that you have a budget amount and an actual cost, at least for some projects, you can create a query with a field that calculates the difference.

1. Create a new query based on the Projects table.
    
2. Include the fields ProjectID, ProjectName, Cost, and Budget.
    
3. Create a new field by typing:
   Compare:[Budget]-[Cost] into the Field cell for a new column.
   This express will produce a positive number when the project's Cost is less than the budgeted amount and a negative number when the project went over budget.

   

4. Run the query.
   Two projects went over budget.
    
   Interestingly, the Compare values are not in Currency format, even though both values used in the calculation were. You can fix that!
    

---

Format

Access has a special function for applying a particular format to your calculated values or to change the formatting of the original values.

Format: Pattern for display
The Format function allows you to choose a standard pattern or create your own pattern for how to display a value.

Syntax for Format function: Format(value, pattern) - takes the value and formats it like the pattern, which can be the name of a standard format or else a custom pattern.

Examples:
Format([Amount],"Currency") takes a value like 87.6 and formats it as $87.60.
Format([Profit]/[Cost],"Percent") takes a value like 0.06457 and formats it as 6.46%
Format([VersionNumber],"###-##-##.###") takes a value like 23456.6789 and formats it as 2-34-56.679

1. Switch to the Query Design View.
    
2. Edit the Compare field to read:
   Compare: Format([Budget]-[Cost],"Currency")
    
3. Run the query.
   Better. The negative numbers are now shown in accounting format, in parentheses.
    
   The values are aligned to the left like text. Wonder why that is?
   And another question... The last two projects look like they are under budget but the $0.00 cost is just the default value. This could be confusing! You need a way to tell when this occurs.
    

---

Conditional Value, IIf

You will create a conditional expression using the IIf function for a new field that will have a value that depends on whether or not the Cost is zero. You used the IIf function in the previous lesson.

Syntax for IIf function:
The general form of an IIf statement has 3 parts.
    IIf(expression, truepart, falsepart)

 where  expression  contains the criteria that you want to evaluate. It must be either True or False.

              truepart  is what you want to see when the expression is True.

              falsepart  is what you want to see when the expression is False.

1. Switch back to Query Design View.
    
2. Add a new field as follows:
   Compare2: IIf([Cost]=0,"Cost not entered",[Budget]-[Cost])
   This conditional expression puts a text string in the field if Cost = 0 and shows the difference between the Budget and Cost otherwise.
    
3. Run the query.
    
   Since one alternative is text, the whole field is treated as text. Again, the number values are not in Currency format.  Perhaps we should create a different IIf to have all of the Compare2 values be actual text.
    

---

Nested IIf

It will take two IIf expressions, one inside the other, to handle the three alternatives - positive number, negative number, Cost = 0.

1. Switch back to Query Design View.
    
2. Edit the expression for Compare2 to read:
   Compare2: IIf([Cost]=0,"Cost = 0",IIf([Budget]-[Cost]<0,"Under","Over"))
   This expression is a conditional inside a conditional.
   Here is the logic:  If Cost is zero, show the text "Cost = 0". If Cost is not zero, look at [Budget]-[Cost]. If that value is less than zero, show the text "Under". If not, show the text "Over".
   This is the method for handling multiple options - nested IIf expressions.
    
3. Run the query.
    
4. Save the query as QProjects-Compare2 and close it.