I have this complex report that is a little non-standard – and here’s how I did it. The general technique is at this other post:
Printing a Repeated Section Message like “Continued”
My function is this:
=IIf( ([txtDetailNum]>11 and [txtDetailNum]=MaxValue([NamedDelegates],[EligibleDelegates]) and [EligibleDelegates]<21) or ([txtDetailNum]=20) or ([txtDetailNum]=40) or ([txtDetailNum]=60), "Continued...","")
The MaxValue function is defined like this:
Public Function MaxValue(a, b)
a = Val(a)
b = Val(b)
If (a > b) Then
MaxValue = a
Else
MaxValue = b
End If
End Function
MaxValue is a lot like the traditional max() but it converts strings to numbers first, because it looks like values in Access reports might become strings.
The logic to show the message works for me, but there’s a bug in there. When txtDetailNum is within a range where the list ends near the bottom of the page, it should show the message, because the footer gets bumped to the next page. That logic is expressed in the first part of the expression:
([txtDetailNum]>11 and [txtDetailNum]=MaxValue([NamedDelegates],[EligibleDelegates]) and [EligibleDelegates]<21)
(The MaxValue part deals with a data glitch when the number of named delegates > eligible delegates.)
So the entire expression should have lines like that throughout in addition to txtDetailNum=20. It just turned out that my data didn’t end in the high 30s or high 50s.
A correct expression would be a bit more complex, and should use VBA. You’d need to define a function that returns true if “Continued…” should be printed. The logic would be something like this:
function printContinue(txtDetailNum) {
pagePosition = txtDetailNum % recordsPerPage
if (pagePosition >= recordThatWouldTriggerBreak) and (txtDetailNum == lastRow) then
return true
else
if (pagePosition == recordsPerPage-1) then
return true
end if
return false
end if
}