In a query you can create new fields that use values from other fields to do calculations and comparisons. The new field is not added to the table design, but it can be used in a form or report.
It is easy to write your own expressions to calculate a new field
in your query using
other field values that are numbers. The general form is the same as for text fields:
NewFieldName:expression
Examples using numbers:
Profit: [Income]-[Expenses]
TotalPrice: [Price]+[SalesTax]-[Discount]
Score: (([Points]+[BonusPoints])/[MaxScore])*100
DiscountPercent: IIf([OrderTotal]>100, 25,0)
Tax: ([Price]-[Discount])*0.0975
Calculated
controls: In the next project, Forms and Reports, you will learn how to
use similar expressions to create calculated controls for a form or
report. However, you name the control in a different way and the
expression must start with the equal sign (=) instead of the new field
name and a colon.
Access has a large number of built-in functions, including standard math and financial functions, plus a number of functions that are special to Access. Quite a number of these special functions are for handling dates and times. Dates/Times are so messy to calculate with that the next lesson will focus on those functions.
Examples:
Sqr([Length]) returns the square root of the value in the field Length
Round([FinalValue],3) rounds the amount in the field FinalValue to show 3 digits to the right of the decimal
Now() returns the current date and time
DatePart ("yyyy", [EndDate]) returns the year part of the date in the field EndDate.
Format(Now(),"mmm") returns the current month as a three character abbreviation.
![]() |
Step-by-Step: Calculated Values - Numbers |
![]() |
What you will learn: | to create a calculated number field to format a calculated value as part of the expression to create a conditional value with IIf statement to use nested IIf statements |
Start with: , Projects database (projects2-Lastname-Firstname.accdb) from previous lesson open.
In the Projects table you have a budget amount and an actual cost, at least for some projects. some projects are apparently not finished yet. You can create a query with a field that calculates the difference.
Access 2007:
> Manage > Back Up Database
Access 2010: File > Save and Publish > Back Up Database > Save As button
Access 2013, 2016: File > Save As > Back Up Database > Save As button
Create a new field by typing:
Compare:[Budget]-[Cost] into the Field cell
for a new column.
To see your typing better: Use the Zoom window.
(Right click on the Field cell and select Zoom.)
This expression 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.
Run the query.
Problem: New column does not show
You must TAB or click out of the column in Query Design View to finish adding the new field definition.
Solution: You must create the new field again.
Inspect the Compare column.
How many projects went over budget?
One - the one with values in parentheses. That is Accounting formatting for negative numbers.
In previous versions of Access the Compare column values did not inherit the Currency data type. It's nice to see that glitches can get fixed.
Another glitch fixed is in records 6 and 7. Previous versions of Access showed a value of 0 in the Compare column even though there was nothing in the Cost column to use. Leaving the Compare column blank when a value is missing is better.
Did you notice that all of the values are in even dollars, no cents? That seems to waste space! You can format the values to hide those zeroes.
Access has a special function, Format, for applying a particular format to your calculated or 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 with the standard Currency format, 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
Edit the Compare field to read:
Compare: Format([Budget]-[Cost],"$##,###")
TAB to the next column.
The pattern is automatically edited, dropping one of the hash marks to the left of the comma. Unexpected!
The pattern tells Access to put the dollar sign $ in front, to use a comma to separate thousands, and to show no digits to the right of the decimal point. Any digits to the right of the original decimal place are rounded.
Perhaps we need to make some other changes to make it clearer which projects are actually finished and which are not and also whether or not the project went over its budget.
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 or null. 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.
You can type the name of this function as IIF, IIf, or iif. Access will revise your typing to IIf.
Add a new field as follows:
Compare2: IIf([Cost]=0 Or [Cost] Is Null,"Cost not entered",Format([Budget]-[Cost],"$#,###"))
Be careful to have a closing parenthesis for each opening parenthesis!
This conditional expression puts warning text in the field if Cost = 0 or is null. It shows the difference between the Budget and Cost otherwise, in the same formatting as before.
Run the query.
The explanation is better than a blank value. But we are not done yet.
You can format the values to handle more than two choices by nesting one IIf expression inside another one. That way you can make Access show something different for each of the three alternatives - positive number, negative number, Cost has not been entered yet.
Edit the expression for Compare2 to read:
Compare2: IIf([Cost] is null,"Cost not entered",IIf([Budget]-[Cost]<0,"UNDER budget","OVER budget"))
This expression is a conditional inside a conditional.
In words: If Cost is empty, show the text "Cost not entered". If there is a value in Cost, look at [Budget]-[Cost]. If that value is less than
zero, show the text "UNDER budget". If not, show the text "OVER budget".
This is the method for handling multiple options - nested IIf
expressions.
There is one spot where expression did not work well. For the My Trips database project, the cost and budget values are the same so the difference is equal to zero. But the Compare2 column shows OVER budget. That's not true! We could nest another IIF statement. We will stop for now.
It would be even better if you could color code the results so you could scan the datasheet instead of having to read the text in Compare2. Unfortunately, that can not be done in a table or query datasheet. In a later lesson we will learn how to do that for controls in a form or report.
Save the query as QProjects-Compare2 and close it.