Avoid Null Values in a Tabular Report
Null values in reports can cause errors, particularly when the field containing the null value is part
of an expression in another control on the report. Instead of simply ignoring the null value and the
resulting errors, you may decide that forcing a zero into the field is preferable.
The following expression in a numeric field??™s ControlSource property will solve this problem.
In this expression, the field is contained in a text box named txtField:
=IIf([Field] Is Null,0,[Field]).
This immediate If statement sets the value of txtField to 0 if the value of Field (the data) is
null; otherwise, txtField is set to the value of Field.
Alternatively, you could create the following function, which performs the same actions:
Function NullToZero(ByVal varValue as Variant)
NullToZero = IIf(IsNull(varValue), 0, varValue)
End Function
664
More Advanced Access Techniques Part III
This function accepts a value (like Field) as the varValue parameter and tests it with the IIf;
then the function assumes the value of varValue or zero, depending on the result of the IIf.
Pages:
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311