Microsoft Access 2010 Bible

Chapter 20: Advanced Access Report Techniques

729

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

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 0 , depending on the result of the IIf. The
benefit of using a VBA function is that you might want to perform some additional operation
within the body of the function, such as substituting a value or logging or notifying the user of the
null argument.

Properly handling nulls is so important that VBA includes a built-in Nz() function to automati-
cally convert potentially null values to 0 or another value. The syntax of Nz() is

= Nz([Field], [ValueIfNull])

Typically, in a ControlSource property you’d provide 0 as the ValueIfNull argument:

= Nz([Field], 0)

Depending on your situation, you may not want to use 0 as the replacement for null values. For
example, if you’re multiplying two fields or variables together, converting one operand to 0 means
the result of the multiplication is always zero. Using 1 as the ValueIsNull argument, at the very
least, returns the other operand’s value.

Inserting vertical lines between columns

You can easily add a vertical line to a report section whose height is fixed (like a group header or
footer). Adding a vertical line to a section that can grow in height (like a Detail section on a grouped
report) is more difficult. It’s really difficult to get a vertical line between columns of a report (see
rptVerticalLines in Figure 20.24). If you simply add a vertical line to the right side of a section
of a snaking columns report, the line will appear to the right of the rightmost column on the page.
You have to be able to specify where vertical lines will appear on the printed page.

Although you add most controls at design time, sometimes you have to explicitly draw a control as
the report is prepared for printing. The easiest approach in this case is to use the report’s Line
method to add the vertical line at runtime. The following subroutine, triggered by the Detail sec-
tion’s Format event, draws a vertical line 3^1 ⁄ 2 inches from the left printable margin of the report:

Sub Detail_Format ()

Dim X1 as Single

X1 = 3.5 * 1440

Me.Line (X1, 0)-(X1, 10000)

End Sub