Microsoft Office Tutorials and References
In Depth Information
Displaying Values That Depend on Conditions
You can create a so-called input mask that determines the formatting of a
calculated text box, as we describe in Book II, Chapter 5. An input mask
can add parentheses and dashes to a phone number or dashes to a Social
Security number, for example.
Displaying Values That Depend on Conditions
Some calculations have an if-then component, which essentially says, “ If
this is true, then do this.” If the order is from your home state, for example,
charge sales tax; otherwise, don’t. If the order is for more than $100,
shipping is free. Access handles these types of if-then calculations by using its
IIf() (immediate-if) function, which we describe in Book III, Chapter 2.
If you charge sales tax only for Vermont orders, for example, you use this
expression:
= IIf([State]=”VT”, [TaxableTotal]*.06, 0)
The condition ([State]=”VT”) is either True or False. If the condition
is True, the expression is [TaxableTotal]*.06 (6 percent of the taxable
total); if it’s False, the expression is 0.
The condition can be a Yes/No field: If the field is Yes (true), the function
returns the first value, and if it’s No (false), you get the second value. The
following expression looks at the Yes/No field TaxExempt to determine
whether this customer is exempt from sales taxes. For taxable customers,
the function returns the value of the TaxableTotal field. For tax-exempt
customers, it returns 0.
= IIf([TaxExempt], 0, [TaxableTotal])
Here’s the mind-boggling part: You can nest functions, including the IIf()
function. That is, you can use a function inside another function. The
following example expression combines the last two examples to calculate sales
tax based on both the customer’s tax-exempt status and the customer’s
state:
Book IV
Chapter 4
= IIf([State]=”VT”, IIf([TaxExempt], 0, [TaxableTotal]*.06), 0)
Formatting Calculated Controls
When you display calculated values on a form, the value isn’t editable in
Form view; you can’t type a different value in its place or delete it. The
expression controls what appears in the text box.
 
Search JabSto ::




Custom Search