PDF or Word Output changes Report totals 1

[CindyK]

I have a couple reports that are almost always output to Word so that minor tweaking can be done before they are emailed. After converting to Access 2007, when the reports are output to Word, totals are often changed in the report - even running sums get changed - I'm guessing Word's "auto correct" kicks in with numbering, modifying the data! I thought a PDF version would solve the problem (altho editing the PDF is a pain). The PDF version no longer changes running totals, but the grand totals get changed. The totals are correct within Access - both in preview and on printed paper.

Does anyone have any solutions or seen this behavior? (The same thing happens whether I keep the database format 2003 or 2007. It does NOT happen on a PC that doesn't have 2007 Office installed.)

Thanks in advance!

 

[dhookom]

Are you using code to create any aggregated values in the report?

Duane
Hook'D on Access
MS Access MVP

 

[CindyK]

Not specifically to calculate formulas - but the underlying query is modified via code before the report is run. There also is code in the open event of the report that modified the grouping and sorting (based on options in a form that is used to print the report).

 

[dhookom]

Is there any difference if you disable the code in the open event?

Duane
Hook'D on Access
MS Access MVP

 

[CindyK]

Oops. In most versions I don't have code calculating totals - but I just came across one that does do a running total with VBA.

On that report, disabling the open event code doesn't do anything - but the field that displays on the print preview starts out with a number 1, sequentially gets increased - then reset to zero on a report grouping.

When I export it to Word, it starts at #35.

I disabled the VBA running total, and now the data totals are of course messed up - but at least they match.

Good grief! I need to be able to use VBA to get those running totals because the grouping is very convoluted (as well as handled via code). Interesting that this worked fine before the 2007 upgrade.

Your posts are pointing of course to the code as the culprit - but I'm also guessing this means I have no workaround short of doing all the work in VBA, dumping rows for output into a temp table, and then producing my report with no code. Ugh... can you think of any other solution? This is a huge problem with about 50 different reports - all reports with considerable complexity (grouping/sorting all controlled via a report console - the goal was to maintain one report file for lots of different options - it seemed an efficient way to go in the past).

 

[CindyK]

We have confirmed that turning off all Word autoformat options "solves" the problem. Of course, that then brings up the problem that users like the autoformat option when in word. Arghh. I believe that's a registry change - it sounds like my next step is to come up with code that enables/disables that. Do you agree?

 

[dhookom]

I haven't found much in terms of aggregated or incrementing values that I can't do without using code. Sometimes setting values back to 0 in the report header or printing without previewing or calculating in the on print vs on format might help.

You have yet to provide any details about your code and requirements.

Duane
Hook'D on Access
MS Access MVP

 

[CindyK]

Hi Duane -

Thanks for your assistance.

The problem with totals getting changed once I output to Word and/or PDF is happening on many reports - all with different code/different total requirements. I could take one example and provide code details, but I thought that would be focusing on a crack when there's a gulf that's a problem! Maybe not though - let me try this again.

All reports have worked for years in Access with no problem. They have always exported to Word with no problem. After the 2007 conversion, they no longer convert to Word or PDF without changing the totals. We have done extensive testing in the last couple days and turning OFF all word autoformat options helps, but still does not guarantee correct results.

In one report where I can easily recreate the problem, I have 2 global variables.

The report header on print event sets
rtClientCntUnique = 0
rtStaffCnt = 0

The report has four groupings.
The header code (on print) of the third grouping is as follows:
rtClientCntUnique = rtClientCntUnique + 1
Me.RTUniqueClient = rtClientCntUnique

rtStaffCnt = rtStaffCnt + 1
Me![rtStaff] = rtStaffCnt

The footer code for the same grouping is:
rtStaffCnt = 0

The unbound field RTUniqueClient is a text box in the fourth group header. The report footer has a bound textbox - =RTUniqueClient.

When I print preview this report in ACCESS, or when I send it to the printer, these totals are correct.

There are three other totals going on in this report, but those are accomplished using the Access built in running totals - and those aren't modified in the PDF version, so I don't think we need to bother with them here.

When I do the export to PDF, the textbox in the report footer gets doubled. When we export it to Word, the number is DIFFERENT - not doubled - but off by a few. We haven't found the pattern for where Word is getting that number from.

In one example I'm working with right now - the correct unique count is 818 clients. The PDF version shows 1636. The Word version shows 820 (with all word autoformat options off).

???

 

[dhookom]

I would attempt to do this either with running sum controls or in the report's record source. For instance if you needed the number of unique customers for each employee in Northwind, you could create

qgrpEmployeeCustomerUnique

SELECT Orders.EmployeeID, Orders.CustomerID
FROM Orders
GROUP BY Orders.EmployeeID, Orders.CustomerID;

qgrpEmployeeCustomerCount

SELECT qgrpEmployeeCustomerUnique.EmployeeID, Count(qgrpEmployeeCustomerUnique.CustomerID) AS CountOfCustomerID
FROM qgrpEmployeeCustomerUnique
GROUP BY qgrpEmployeeCustomerUnique.EmployeeID;

You can then join qgrpEmployeeCustomerCount into a report record source to have the unique number of customers for each employee.


Duane
Hook'D on Access
MS Access MVP

 

[CindyK]

Thanks Duane. That's good advice. I have about 40 reports, which in reality translate to about 300 reports because each report "template" can be grouped and/or sorted in about 8 different ways (all done via code so that I don't have to have 300 report objects). The record source is also set via code - the user picks from about 25 different 'filter options' before running the report - ie which program, which staff, which type of customer etc.

Maybe I can add to that code populating a temp table with just the totals and then link to that - something I didn't think of until reading your answer, so thanks!

 

[dhookom]

Sounds like you have a good handle on your report specifications and a solution planned. I don't care much for temporary tables but they are often the most efficient method (speed and maintainability) for creating complex reports. At times, I will create a temporary MDB file on the fly to store the temporary tables.

Duane
Hook'D on Access
MS Access MVP