Last Updated 17 Jan 2019


I recently encountered a strange issue on a report with a very large number of records
Filtered = 1,758,312 records; Unfiltered = 2,604,721 records

In both cases the standard 'page of pages' footer shows as a negative number.
Also peculiar is that the filtered report with fewer records is more negative



I found out that there were 47 records per page on this report, so I calculated the correct result to be as follows:



The reason for this behaviour is that the built in Pages function is an integer value.
The total pages exceeds the integer value limit (32767) and had restarted from the negative integer limit (-32768)

I raised this issue in a post at Access World Forums and I got a response from fellow AWF moderator jdraw with the following solution based on an answer found at stackoverflow:

Replace the standard [Pages] in = [Page] of [Pages] with:

="Page " & [Page] & " of " & IIf([Pages]<1,(32768-Abs([Pages]))+32768,[Pages])

The Abs function calculates the absolute value of a number ignoring the sign.
For example: Abs(15455) = 15455 ; Abs(-15445) = 15445

So, for example, if the reported [Pages] = -15445, the IIf expression becomes
(32768-Abs(-15445)+32768 = (32768-15445) + 32768 = 55420

This worked perfectly giving EXACTLY the number of pages that my earlier calculation predicted.


I just hope nobody ever tries to actually print this report with 55420 pages!

NOTE:

The total integer range allows for 65536 values from -32768 to +32767
Therefore, if an even larger report has 65536 actual pages, Access would normally report total [Pages] as 0
Using the above calculation would give (32768 -0) + 32768 = 65536

Clearly the expression will fail if the actual number of pages was even larger than that!
Hopefully, in the real world that will never happen

However, I did two further tests on the larger dataset of 2,604,721 records increasing the spacing between records in order to have fewer records per page.

Firstly, I reduced the records per page to 35 giving a total of 68546 pages
The [Pages] expression shows 3010 and the corrected expression says 68546.
Of course, 65536 + 3010 = 68546.

Next, I changed the report to 14 records per page giving a total of 186,052 pages
The [Pages] expression shows -10556 and the corrected expression says 54980.
A quick calculation shows 65536 + 65536 + 54980 = 186052

This means Access is just cycling repeatedly back and forth through the entire integer range



Colin Riddington           Mendip Data Systems                 Last Updated 17 Jan 2019