MS Access: Showing “Continue…” Conditionally at the bottom of a Section in a Report

Maybe I’m missing something – but it looks like Access doesn’t have this feature – to put “Continued…” or “More…” at the bottom of a section if the next section is on the next page. If it exists, please comment or email me at johnk@ the domain name of this site. I seriously hope it exists.

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
}