Sometimes, with reports, there are certain exceptions to an expression's results you wish to change if the value of a certain field is null, and you need to take charge. Nz or IIf can help with this.
Suppose you have a report which will show the employee number, as #xxxx. (Assume a field name of [employee_number]. However, for some records, there is no employee number, hence the {employee_number} field is blank. In these situations, you don't want the # sign printing out.
One way this can be handled is with the Nz function, or with the IIf function. Here are some examples.
BeforeThis is the "control source" for a text box on a report. If there is an employee number then you will see, say, #3510. However, if there isn't, you will see the # character without a number beside it. What we want to do is specify that, if employee_number doesn't have a value, then don't show the # character.
The above expression would now read:
AfterI removed the # sign from the " " portion & then added the new code in its place.
You have a text box on a label-report which will show employee number as (3512) but if there are no employee numbers you have () characters appearing which are unsightly. You need to specify that, in that case, the ( and ) characters shouldn't appear.
The default expression looks like this:
=Trim([first_name] & " " & [last_name] & "(" & [employee_number] & ")"The "(" and ")" portions need to be replaced with a modified expression which will prevent ( and ) from appearing if employee_number doesn't have a value.
The modified expression:
=[first_name] & " " & [last_name] & IIf(IsNull([employee_number]),""," (" & [employee_number] & ")")The portion that performs this calculation (example 2):
IIf(IsNull([employee_number]),""," (" & [employee_number] & ")")The generic “template” syntax:
IIf(IsNull([field_name]),expression if null, expression if not null)Expression if null:
""Expression if NOT null:
" (" & [employee_number] & ")"You're trying to perform calculations on a report, but when you add together the values, the total is blank if any of the line items don't have a value. (Microsoft also talks about that in this article they wrote.) You use the Nz to accommodate that.
You're trying to sum up the line items of [inside_cost], which always has values in them (so no problems there); however, you're also adding up the value of [txtAddMatTotal], which often-times is left blank. You need to assume 0 for that field in that case, otherwise, the total field totally blows it.
We need to specify that when txtAddMatTotal is left blank, to properly assume 0 for it.
For more on handling Null scenarios, this article appears to offer quite a lot of information, too.