UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Calculated Field Different: Preview Vs Report, Access 2016    
 
   
sfrvn
post Dec 10 2017, 02:41 PM
Post#1



Posts: 15
Joined: 10-December 17



In Report mode, a calculated textbox displays correctly. The red arrow in attached file (report.jpg) points to textbox - 570. Value of that textbox is SUM of Categories (indicated by blue arrows). [Report uses Grouping at Category level.]

In Report Preview mode, this calculated textbox does not display correctly. The red arrow in attached file (preview.jpg) points to textbox - 220 which is the value for the first report Category. This textbox does not display the SUM of all Categories. Only first Category value (indicated by blue arrow) is displayed.

When I print this report (whether from the Report view/mode or the Preview mode), the printout always displays as in Preview mode. That is, this important textbox does not display the Category sum.

I have searched for days trying to find some solution, or insight into a solution, for this problem. I have tried numerous approaches to 'fixing' this problem... Requery, Repaint, control source = TempVars, different report/control events... but nothing has worked. Perhaps there is no solution.

So I am now turning to the UA 'collective wisdom'. Any ideas would be appreciated.
Attached File(s)
Attached File  preview.jpg ( 57.81K )Number of downloads: 7
Attached File  report.jpg ( 54.01K )Number of downloads: 10
 
Go to the top of the page
 
GroverParkGeorge
post Dec 10 2017, 03:04 PM
Post#2


UA Admin
Posts: 32,357
Joined: 20-June 02
From: Newcastle, WA


Welcome to UtterAccess.

How is the calculation done? Is it in an expression in the control? Is it done with a property in the Property Sheet?

One thing to keep in mind, of course, is that Print Preview is SUPPOSED to be the same as the actual printed report, so that behavior is expected.

We have a better chance of helping you diagnose the problem if we can see the actual report, not just a screenshot.

Can you share that accdb with us to review? Perhaps a version with only enough sample data to see the problem. Remove all sensitive data. Compact and repair it, then compress it into a zip file; upload it here.

Thanks.
This post has been edited by GroverParkGeorge: Dec 10 2017, 03:27 PM

--------------------
Go to the top of the page
 
sfrvn
post Dec 10 2017, 04:22 PM
Post#3



Posts: 15
Joined: 10-December 17



Thanks for your reply, George.

Your comment about preview mode looking like the printout made me chuckle. And you were kind by not being snarky about it. <smile!> Guess that is why they call it "print preview"??

My effort to calculate the sum of all Categories presented in the query results is derived thru a serpentine process. Rather than try to verbalize this convoluted mess, I present an annotated image of the Design view. I can explain/provide more info once you have had an opportunity to look this over.

To my novice Access eye it seems that, in Report view, the report is allowed to update entirely -- perhaps "fully load"?? But in Preview mode my serpentine process never completes.

Thanks for investing your time and expertise to help this novice.
This post has been edited by sfrvn: Dec 10 2017, 04:27 PM
Attached File(s)
Attached File  controlInfo.jpg ( 163.28K )Number of downloads: 9
 
Go to the top of the page
 
GroverParkGeorge
post Dec 10 2017, 04:47 PM
Post#4


UA Admin
Posts: 32,357
Joined: 20-June 02
From: Newcastle, WA


Unfortunately, screenshots are not really an efficient way to diagnose problems involving calculations. We can see what you want to have happen to a limited extent. But seeing what actually happens is a different thing.

Is it possible to provide a sample db for review?

--------------------
Go to the top of the page
 
sfrvn
post Dec 10 2017, 06:40 PM
Post#5



Posts: 15
Joined: 10-December 17



George, again thanks.

I asked the boss about sending a copy out in order to garner your help. But he basically said 'No... figure it out yourself'.

I feel I have identified the underlying issue and it deals with the differences between Preview and Report. I've looked at the 'Order of Events' for both report 'modes', but that offered no obvious clue. Also, why is it (rhetorically speaking) that when User opens in Report view, all is well. Yet, select "Print report" and the report transitions to Preview mode with the incorrect value displayed. [That is where/when I tried setting and using a TempVar.)]

If I can find a way to print directly out of Report mode -- without going through Preview, all should be well. Almost like a 'screenshot'. Note that I tried SelectObject method to select the report (rptBudgetAnalysis2) and Print.Selection -- which printed out my Login form! LOL

So... please do not spend any more time/effort on this, George. You've been very kind. I thank you for your willingness to help!

And btw... kudos on surpassing 30,000 posts!! I bow down in envy *and* jealousy.

gary b
Go to the top of the page
 
GroverParkGeorge
post Dec 10 2017, 08:47 PM
Post#6


UA Admin
Posts: 32,357
Joined: 20-June 02
From: Newcastle, WA


Since the interaction that determines what values are displayed occurs when the report is open--either in print preview or report view--just looking at screen shots is not definitive.

I will say that events in the report DO fire when a report is opened in print preview, but not necessarily in report view.

It's entirely likely your analysis is right, although I have no way to verify that, or offer suggestions, unfortunately.

--------------------
Go to the top of the page
 
projecttoday
post Dec 10 2017, 09:16 PM
Post#7


UtterAccess VIP
Posts: 9,364
Joined: 10-February 04
From: South Charleston, WV


I would be interested to know what would happen if you moved that textbox to the report footer (for testing purposes).

Convoluted?

My experience with Access reports has been that Print Preview is usually correct.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
sfrvn
post Dec 13 2017, 07:15 PM
Post#8



Posts: 15
Joined: 10-December 17



Greetings, all...

I've been fighting windmills for the past couple days -- pursuing my desire to print a report as it appears in acViewReport. Dozens of web pages and blog posts later, I'm convinced that it is not possible to print the report as shown in acViewReport ( 'Report View' ).

Contributions here by others have helped me understand Access reports -- especially the limitations! As I now understand it, Print Preview is NOT the report you observe under acViewReport. Print Preview shows you how the report will look! As I describe it, Print Preview does not fully refresh/recalculate controls. It simply shows how the report is formatted -- colors, positions, etc. It correctly lists/shows the subordinate elements of each Category. It is as though a final refresh/repaint/re?? does not run, resulting in my running sum not appearing in my textbox.

As an example, one of my primary objectives is to show the User how much was budgeted for an expense category compared to how much was actually spent for expenses (Sub-category details) assigned to that Category. In Report View, a textbox (tbxTotBudgeted in Page Header) displays a running total -- the sum of all Category budgeted amounts. If the report view displays five Categories (based on criteria), this textbox will display the SUM of the amount budgeted for these five Categories. Works perfectly! Directly below this running total is the sum of all expenditures -- the total amount spent on the sub-category details. (Seen in screenshots posted previously) The User can easily see and understand how well they stayed within budget!

But in acViewPreview, tbxTotBudgeted is assigned and displays the budget amount assigned to the first (and only the first) Category. All Category budgeted amounts (blue in screenshot) are displayed. But they are not aggregated through the length of the report. If the report extends to multiple pages, tbxTotBudgeted again displays only the budgeted amount of the first Category listed on the page.

I have posted this solely to provide others (who may be chasing these same windmills!) an opportunity to see my perspective -- and maybe save them some time!

If any UA Member comes across info pertaining to printing a report as it appears in acViewReport, please add you input here. Thanks to all for your patience and willingness to help!!

gary b

BTW... I tried putting the textbox (tbxTotBudgeted) in other sections/positions, but it did not help.

Go to the top of the page
 
GroverParkGeorge
post Dec 13 2017, 09:37 PM
Post#9


UA Admin
Posts: 32,357
Joined: 20-June 02
From: Newcastle, WA


Perhaps we haven't really communicated effectively yet.

Report View is for ON-SCREEN viewing only. It is NOT printed. That much you've confirmed, I believe.

Print Preview is an on-screen preview of what the final printed version should look like when sent to a physical printer.

Given that distinction, you really can not expect to send Report View to a physical printer. Again, you've confirmed that.

Nor can you expect the two versions to exhibit exactly the same properties and behaviors.

Your problem with this report appears to be that some controls populate differently between print and report views. Frankly, given the fact that some events (On Print, for example) DO fire when a report is printed, but not when it is displayed on-screen, and vice versa, this is quite understandable as well.

I am not surprised, given the two different environments, at this result.

What is a bit surprising is that you can't generate the running sum as you describe it in the printed version.

To diagnose that problem, it would be VERY helpful to have a copy of your accdb to review. Since your boss refuses to permit that, we're at an impasse. I'm not sure whether his objection is to sharing data or sharing the code. If the former, give some consideration to creating dummy data to illustrate the problem without giving away confidential information. If the latter, I can assure you that there's very little really unique code out there which would suffer from being shared, especially since it appears there's a problem in the way this report works now.

Thanks.

--------------------
Go to the top of the page
 
sfrvn
post Dec 14 2017, 10:03 AM
Post#10



Posts: 15
Joined: 10-December 17



Good morning, George...

I have attached a stripped down version of the dB for you to look at. I appreciate your comments. And your approach is different than mine in that you want to know why the textbox is not populating in Preview as it does in Report view. Until now, I have insisted on making Report view print -- something Access is not intended to do. (My wife calls that 'stubborn'; I call it 'persistent'!) Your approach is within normal Access operation and will -- if successful -- accomplish my goal. So a tip-o-my-hat to you, sir.

Notes:
1. login as User: password user
2. Select Budget
3. The Report BUTTON displays the actual budget -- the Categories and the sub-categories (AKA Details). This is NOT the issue.
4. My problem arises when the User wants to compare what was spent during the month to what was allocated (that is, 'budgeted') for that month.
-- Select a month (pick 9) and click gray Compare: button.
-- Report will open in Preview mode. [Does not matter which way button is coded: open in Report or Preview mode.]
-- take note of green row -- Total Budgeted: Displays 220.00
5. RIGHT-click report title bar and select Report View
-- take note of green row -- Total Budgeted: Displays 570.00

There, young man, is my problem.



Attached File(s)
Attached File  BudgetHero_UA_.zip ( 580.45K )Number of downloads: 3
 
Go to the top of the page
 
GroverParkGeorge
post Dec 14 2017, 10:46 AM
Post#11


UA Admin
Posts: 32,357
Joined: 20-June 02
From: Newcastle, WA


Okay, I've come to the conclusion that using Access is not a matter of creative, innovative, awe inspiring solutions. It's a matter of figuring out how best to leverage its existing strengths and avoid its weaknesses.

A friend of mine who runs one of the more successful Access/.net shops likes to compare Access to canoeing on a river. Going upstream, against the current, is hard work and requires a lot of paddle-work. Going downstream, with the current, is easy and requires only judicious use of the paddle to stay on course.

We'll take a look and see what we can see.
Thanks.

--------------------
Go to the top of the page
 
GroverParkGeorge
post Dec 14 2017, 01:16 PM
Post#12


UA Admin
Posts: 32,357
Joined: 20-June 02
From: Newcastle, WA


Unfortunately, inappropriate table design is at the heart of the problem.

I've tried to figure out a way to salvage this without major table redesign, but it's just going to end up so convoluted that any future changes or additions will cascade into further problems.

The heart of it is that the budget table includes TWO kinds of values: individual items and "Cat Totals", which are, in fact, not really totals for the category anyway. This sort of invalidates any attempt to get at the values via standard aggregation. Unfortunately, there's a further problem in the way the report works. There are budget items that don't show up in the ledger. I won't pretend to be an expert in accounting, but I'm just not sure why that would be.

For example, you have budget items for "Food", but no corresponding ledger items.

Given that, I'm not sure it's worth much more time investment.

As you know, the reason the Report View does work is that you're using control references within the same report to get values for other controls. That's not unusual, in itself, but here, placing the Report Total in the page header BEFORE any calculations are done for categories after the first one prevents the print version from getting those values for later categories. In the Report View, it is okay because the entire report loads and displays at once.

There may be a way to salvage this, using a technique I've read about, but never implemented. It would involve -- for Print and Print Preview -- sending the report all the way to the end twice, once to get the total calculations and a second time to put them in the up-top controls. It might be a work around here.

However, what I really think you ought to do is invest some time in learning about how to use relational tables in a relational database. It's called normalization. It's the design process we use to create tables.

You can learn about it here.



--------------------
Go to the top of the page
 
GroverParkGeorge
post Dec 14 2017, 02:00 PM
Post#13


UA Admin
Posts: 32,357
Joined: 20-June 02
From: Newcastle, WA


Actually I thought of a workaround that seems to return a valid result.

You really need to address the structural problems I called out. But it's a Band-Aid that seems to work.

Attached File  BudgetHero_UA_.zip ( 546.15K )Number of downloads: 6


--------------------
Go to the top of the page
 
sfrvn
post Dec 14 2017, 03:46 PM
Post#14



Posts: 15
Joined: 10-December 17



Thanks for your insight, George.

I (obviously) am no Access expert. I do understand the concept of relational database. I just did not see the need for it when I started this database. You cite this as a major shortcoming, so I will 'normalize' the structure. Somewhat in my defense, this project simply grew -- from what started as a simple 'check register' -- then to add a budget -- and then the comparison of how well expenditures track with allocated funds. Then the database was further expanded to track two other, separate, financial accounts. Looks like I ignored all the Project Management tenets I learned decades ago.

The short version is that, based on your recommendations, I will go back and review database design and structure. I think it is equally obvious that I would have pursued the Report/Preview issue oblivious to the fact that the real problem was design.

Thanks again for your insight and guidance, George. Perhaps I can returned the favor some day.

gary b
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    19th April 2018 - 10:24 PM