Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 03-16-2019, 05:38 AM   #1
senseidan
Newly Registered User
 
Join Date: Jan 2018
Posts: 38
Thanks: 13
Thanked 0 Times in 0 Posts
senseidan is on a distinguished road
weird behaviour of calculated fields

I have created a report that displays with some delay the results of it's calculated fields. It's about 15-20 seconds delay that I assumed was due to too much computing effort. I thought it was normal because, during this time, simple arithmetic operations were performed with about 20,000 records. So I focused on finding ways to speed up this process but, to my total stupefaction, I accidentally spotted that if I click on the calculated fields, the results are there, without any delay. Please help me understand what's going on and how I can "convince" those fields to show the results when they are available, without this weird delay.

senseidan is offline   Reply With Quote
Old 03-16-2019, 07:18 AM   #2
senseidan
Newly Registered User
 
Join Date: Jan 2018
Posts: 38
Thanks: 13
Thanked 0 Times in 0 Posts
senseidan is on a distinguished road
Re: weird behaviour of calculated fields

UPDATE: I noticed that the delay only appears on Report View (not in Print Preview) and it seems it is related to the display of the results rather than their calculation because when I try to scroll, even just a little, the results disappear and when I stop scrolling they reappear after the usual delay.
senseidan is offline   Reply With Quote
Old 03-16-2019, 01:33 PM   #3
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 2,616
Thanks: 29
Thanked 636 Times in 619 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: weird behaviour of calculated fields

Hi. Are you performing the calculations in the report or in the query? Just curious...

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 03-16-2019, 03:54 PM   #4
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,825
Thanks: 17
Thanked 345 Times in 342 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: weird behaviour of calculated fields

I have noticed similar issues at times when ACCESS is doing the same process repeatedly for... reasons... that is isn't sharing with me. You may find doing your calculation ONCE prior the report opening and assigning it to a variable, then just referencing the variable will cause this delay to go away.

Alternately, you can work through everything that is calling your code to find where it is being called multiple times, if this is actually the cause.
Mark_ is offline   Reply With Quote
The Following User Says Thank You to Mark_ For This Useful Post:
senseidan (03-17-2019)
Old 03-17-2019, 03:10 AM   #5
senseidan
Newly Registered User
 
Join Date: Jan 2018
Posts: 38
Thanks: 13
Thanked 0 Times in 0 Posts
senseidan is on a distinguished road
Re: weird behaviour of calculated fields

@theDBGuy
Hi,
Most of the calculations are made by queries and a small part is made in the report.
To be more precise, what I'm trying to do is a kind of cash flow. My report should show in detail the current day transactions and finally show how much money I have, based on all previous transactions.
Let's say that on the first day I get $ 100 out of 10 transactions and spend $ 30 out of 5 transactions. At the end of the day I have $ 70.
The next day, I collect $200 out of 5 transactions and spend $40 out of 2 transactions. At the end of the day I have $160 plus $70 of the previous day = $ 230.
My strategy was to collect the necessary data from the tables that contained them and to create a union query. Then, another query makes the calculations up to the current day to find out how much money the day started.
Finally, in the report, I detail the transactions of the day, make the calculations for that day only, and I add the result of the previous days ( DLookup in the query which made all calculation except current day)
So, can I have yours "2cents"?
senseidan is offline   Reply With Quote
Old 03-17-2019, 06:04 AM   #6
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,766
Thanks: 40
Thanked 3,490 Times in 3,377 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: weird behaviour of calculated fields

a lot depends on whether your tables are structured correctly and properly indexed. From your description you are using quite a complex method for something which should be fairly straightforward. This may be due to an inherent structural issue or perhaps method of approach.

Assuming your transactions table uses these columns

tblTrans
TranPk...TranDate...TranDesc….TranAmount
1...……….1/1/2019...deposit......200
2...……...2/1/2019...expense1...-30
3...……...2/1/2019...expense2...-20
4...……...3/1/2019...expense3...-100
etc

TranID and TranDate should be indexed

This sql will give you opening balance and each transaction for the specified day

Code:
PARAMETERS [Enter Date] DateTime;
SELECT IIf([tranDate]<[Enter Date],[Enter Date],[TranDate]) AS TransDate, IIf([tranDate]<[Enter Date],"Opening Balance",[TranDesc]) AS TransDesc, Sum(tblTrans.TranAmount) AS TransAmount
FROM tblTrans
WHERE (((tblTrans.TranDate)<=[Enter Date]))
GROUP BY IIf([tranDate]<>[Enter Date],[Enter Date],[TranDate]), IIf([tranDate]<[Enter Date],"Opening Balance",[TranDesc]), IIf([tranDate]<[Enter Date],0,1)
ORDER BY IIf([tranDate]<[Enter Date],0,1)
For the closing balance, you can have a control on your form or report to sum the TransAmount field
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
senseidan (03-18-2019)
Old 03-17-2019, 09:13 AM   #7
senseidan
Newly Registered User
 
Join Date: Jan 2018
Posts: 38
Thanks: 13
Thanked 0 Times in 0 Posts
senseidan is on a distinguished road
Re: weird behaviour of calculated fields

@Mark
Thank you very much! Your suggestion, to do (all) calculation prior the report opening, WORKED!
I have deleted all the calculations in the text boxes and used temporary variables to bring the results into the report. Now all results appear instantly, as they should.
It was strange to note that even in text boxes where there were no calculations (DLookup only) there were delays and now, using an indirect method (assigning a DLookup value to a variable then instructing the report to use the value of that variable in the text box), I no longer perceive any kind of delay.
You were right, MS Access is doing something behind the curtain and not telling us what.

senseidan is offline   Reply With Quote
Old 03-17-2019, 11:29 AM   #8
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,825
Thanks: 17
Thanked 345 Times in 342 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: weird behaviour of calculated fields

Very very glad it all worked out!

For data normalization, do you have one table that you enter journal entries into or is this a "One table for IN, one table for OUT" arrangement?
Mark_ is offline   Reply With Quote
Old 03-18-2019, 03:48 AM   #9
senseidan
Newly Registered User
 
Join Date: Jan 2018
Posts: 38
Thanks: 13
Thanked 0 Times in 0 Posts
senseidan is on a distinguished road
Re: weird behaviour of calculated fields

Hi Mark,
Me too I am happy and a little amazed by the result.
I would have liked to understand what was going on, but now I'm more concerned about the problem that you and CJ London have raised (sort of).
I'm thinking if I can rely on the method of calculating my balance each time I view the report. Now there are about 20,000 transactions but this number will increase over time. In addition, and here I approach the answer to your question, I have to compile the data from three different tables, before the calculations, each containing inputs and outputs. It's a situation that does not thank me but I can not avoid it.
senseidan is offline   Reply With Quote
Old 03-18-2019, 04:38 AM   #10
senseidan
Newly Registered User
 
Join Date: Jan 2018
Posts: 38
Thanks: 13
Thanked 0 Times in 0 Posts
senseidan is on a distinguished road
Re: weird behaviour of calculated fields

@CJ London
I want you to know that I still analyze your answer even if it does not exactly match my situation. You're right, I've inherited a situation that has made me make things more complex. But I do not claim to have found the best solution. I want to test the code you sent to me and figure out if I can find a solution for the situation when the calculations will become more numerous as the transactions are added. Until then, thank you for the magnitude of your response.
senseidan is offline   Reply With Quote
Old 03-18-2019, 06:30 AM   #11
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,825
Thanks: 17
Thanked 345 Times in 342 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: weird behaviour of calculated fields

To find out where you are getting this massive slow down, one thing you can do is put a little checking code in your current code. I'd have labels on the report that are set to "0" to begin with. Each time a piece of code is hit it updates its matching label by adding 1 to it. This SHOULD let you quickly find out of one or more pieces of code are getting hit repeatedly. Call it a 15 minute "Sanity check" if you will.

Mark_ is offline   Reply With Quote
The Following User Says Thank You to Mark_ For This Useful Post:
senseidan (03-18-2019)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Weird Stored Procedure Behaviour SkyCraw Modules & VBA 11 05-01-2015 06:19 AM
Weird Paste (append) behaviour HGMonaro Tables 2 02-04-2015 03:23 PM
SQL Trigger Causing Weird Behaviour in Access Front End... JonV72 SQL Server 5 06-19-2013 09:10 AM
Weird behaviour, focus dissapeared bignose2 Modules & VBA 1 03-02-2012 01:02 PM
Weird Access behaviour (can't find form) AngelsGuardian Forms 3 01-27-2006 05:46 PM




All times are GMT -8. The time now is 03:01 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World