Mendip Data Systems

Database applications for businesses and schools

LogoTransparent

Code Samples for Businesses, Schools & Personal Use

Negative Total Pages on Report

Return to Code Samples Page

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

 

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

NegativePages1 - Summary NegativePages2-Calcs NegativeTotalPages3 -Fixed

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 report total [Pages] as 0

The above calculation would become (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 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