 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Reports / Printing / May 2008doing calculations using Query Vs Report using Access
Hello I am running a MS Access database from a server connected to several PCs over a LAN. I generated a report which does very simple maths, sums and divisions - strangely the report results are only calculated correctly on some (ie: not all) PCs! It never occurred to me that some PCs can get the maths wrong!! I managed to resolve this problem by doing the calculation in a query, so really I am after feedback from anyone else who has experienced this before. I'd be quite happy to explain this in more detail if required. Look forward to hearing from you. Buddy Kadri What is different about the PCs that give different results? Is is possible data is changing between the time the first PC runs the report and the next one? In other words, are you positive all have the EXACT same data and the EXACT same version of your app? What you are describing is highly unlikely. As a rule, it is better to do the calculations in the report rather than the query. It is faster because Jet is a file server database engine, not a client server database engine like SQL Server. That means to execute a query, the entire recordset has to be loaded from the server to the PC, and there is a round trip for each calculation. I once converted a report that had a lot of complex calculations in the query and took an hour to run and moved the calculations to the report. It ran in under 5 minutes.  SignatureDave Hargis, Microsoft Access MVP > Hello > [quoted text clipped - 11 lines] > > Buddy Kadri I agree with Dave. My suspicion is your calculations are being performed in code within the On Format event of the report. If so, the results may vary based on users having different default printers. SignatureDuane Hookom Microsoft Access MVP > What is different about the PCs that give different results? > Is is possible data is changing between the time the first PC runs the [quoted text clipped - 26 lines] > > > > Buddy Kadri Good insight, Duane. I didn't think about that. SignatureDave Hargis, Microsoft Access MVP > I agree with Dave. My suspicion is your calculations are being performed in > code within the On Format event of the report. If so, the results may vary [quoted text clipped - 30 lines] > > > > > > Buddy Kadri Hello everyone and thank you for the swift reply. I agree with you entirely, this scenario is very strange and has surprised me very much but I can assure you it is happening! All PCs are running WinXP and have the same default printer and data is not changing in the meantime.. I am more than happy to share whatever info you need to help resolve this. For example, would it be possible for me to export a report I designed to demonstrate the problem? Please advise what I need to do next.. Buddy Kadri > Good insight, Duane. I didn't think about that. > [quoted text clipped - 32 lines] > > > > > > > > Buddy Kadri Some things to consider: Are all PCs on the same version of Windows? Are all PCs on the same version of Access? Are all PCs on the same Service Pack for Windows and for Access? Are all PCs running full Access or are some on Access Runtime? Are all PCs on the same version of Office? Have you compiled your application? Do you use Option Explicit in your VBA code? Are all using mdb or mde? If you could tell us the Access version and Windows version it would be helpful. Also post an example of the offending calculations and the different results returned by the calculation. SignatureDave Hargis, Microsoft Access MVP > Hello everyone and thank you for the swift reply. > [quoted text clipped - 44 lines] > > > > > > > > > > Buddy Kadri Hello Klatuu Some answers for you:- >Some things to consider: >Are all PCs on the same version of Windows? Yes Are all PCs on the same version of Access? Yes Are all PCs on the same Service Pack for Windows and for Access? Yes Are all PCs running full Access or are some on Access Runtime? All running full Access Are all PCs on the same version of Office? Yes Have you compiled your application? No, I also do compact and repair regularly. Do you use Option Explicit in your VBA code? I have no idea, sorry. Are all using mdb or mde? all are using mdb. I have a couple of JPEGs I can give you together with any part of the database itself you like to demostrate the problem.. Best regards Buddy > Some things to consider: > Are all PCs on the same version of Windows? [quoted text clipped - 59 lines] > > > > > > > > > > > > Buddy Kadri I don't need the jpegs or the database, but it would be good if you could post the code that does the calculation the returns inconsistent results and the different values the calculation is returning. The Option Explicit statement should be included in the Option statements at the top of every VBA module, including modules, class modules, report modules, and form modules. It cause VBA to require that all variables be Dimmed. That way, it ensures there are no misspellings and that the variable scope is correct. Start with this: Add the Option Explicit statement to all your modules including standard modules, report modules, and form modules. Compile your code to be sure there are no errors. Compact and repair your database. Make sure all PCs have a copy of the changes. Test the results of the calculations. Let me know how it goes. If that does not resolve the problem, please post back with the code and the results. SignatureDave Hargis, Microsoft Access MVP > Hello Klatuu > [quoted text clipped - 102 lines] > > > > > > > > > > > > > > Buddy Kadri Hello Dave I use Expressions in a report to do the calculation, there is no visible code as such for me to post. Sorry for my ignorance, I know very little about Visual basic. It looks to me that what I am reporting here is a high level bug in Microsoft Access?! I've been creating customised databases using MS Access on and off for almost 10 years and I've avoided learning much about VBasic! Regarding the option explicit statement that is being referred to, I do not have anything under my database Modules tab. I only use tables, queries, forms, reports, pages and Macros. I don't know where to go from here.. Buddy > I don't need the jpegs or the database, but it would be good if you could > post the code that does the calculation the returns inconsistent results and [quoted text clipped - 124 lines] > > > > > > > > > > > > > > > > Buddy Kadri How about providing some of the "Expressions in a report to do the calculation", sample data, desired results, and un-desired results. Clearly, three of us attempting to help you have never had a miscalculation in a report that uses expressions (no code) where we haven't made an error in the expression. SignatureDuane Hookom Microsoft Access MVP > Hello Dave > [quoted text clipped - 139 lines] > > > > > > > > > > > > > > > > > > Buddy Kadri Hello Duane Hope you prove me wrong because I can't identify a mistake in the Report Expressions which I use - why the Report displays different results on different PCs beats me! I shall try to explain. My Testdata table contains several fields, here is a list of the fields I think are relevant to explaining where the problem lies: SerialNumber (Text) TestQty (Integer) Pass? (Yes/No) Retest? (Yes/No) Return? (Yes/No) WO Number (Long Integer) pstk Part Number (Text) I have a totals SumOfTestQty query which works correctly on all PCs. It filters by "WO Number",1st pass and no Returns from the field (ie: both "Retest?" and "Return?" set to filter out Zero/No). I have 2 text boxes in my report footer which try doing the same simple calculation, but consistantly display the incorrect results on some PCs! The report takes data from a query of queries all filtering by "WO number" and by Return?=Zero/No. The report details "TestQty", "Serial Number" , "Pass?" and "Retest?" tick boxes and Running Sum = Over All and other fields which I think are not directly relevant to the maths. In Report Detail tab I also have text box "TotalTestedThisBatch" which sums up the passes ie: =-1*[Pass?]*[TestQty] and "Total_1stTimePass" which XORs "Pass?" and "Retest?" to sum up the 1st time pass Test Quantity ie: =-1*([Pass?] Xor [Retest?])*[TestQty] - both of these are Running Sum = Over All in Report Details tab. I then have the two fouling text boxes in the Report Footer, which display the results of these 2 text boxes. Sample data from query is below, please copy and paste into a full size window text file to review formatting properly :o) These are the desired results where SumofTestQty and Yield are always correct. The undesired results are only viewable in Report Mode so I can email as attachement if you let me know how, but basically the reported data give undesired equivalent to SumofTestQty of 25 and 100% yield! By the way this is not a printer driver issue, wish it was! ----- SerialNumber Return? pstk WO Number TestQty Pass? Retest? AllTestedNoReturns Yield Total1stPass SumOfTestQty 0 137-903TST 10127 25 -1 0 83 0.951807228915663 75 79 0 137-903TST 10127 1 -1 0 83 0.951807228915663 75 79 0 137-903TST 10127 3 -1 0 83 0.951807228915663 75 79 0 137-903TST 10127 1 -1 -1 83 0.951807228915663 75 79 0 137-903TST 10127 1 -1 -1 83 0.951807228915663 75 79 0 137-903TST 10127 1 -1 -1 83 0.951807228915663 75 79 0 137-903TST 10127 1 -1 -1 83 0.951807228915663 75 79 0 137-903TST 10127 21 -1 0 83 0.951807228915663 75 79 0 137-903TST 10127 1 0 0 83 0.951807228915663 75 79 0 137-903TST 10127 1 0 0 83 0.951807228915663 75 79 0 137-903TST 10127 1 0 0 83 0.951807228915663 75 79 0 137-903TST 10127 1 0 0 83 0.951807228915663 75 79 0 137-903TST 10127 25 -1 0 83 0.951807228915663 75 79 ----- Thanks Buddy > How about providing some of the "Expressions in a report to do the > calculation", sample data, desired results, and un-desired results. [quoted text clipped - 146 lines] > > > > > > > > > > > > > > > > > > > > Buddy Kadri Any feed back as to where the problem lies? How can there be an error in the expressions I use and how come some PCs (same network, software, printer drivers etc..) miscalculate and others are ok? If there is an error in the expressions I use then I need to find it! Is there any chance you can point me to someone who can help? Thanks ---------- Clearly, three of us attempting to help you have never had a miscalculation in a report that uses expressions (no code) where we haven't made an error in the expression. Duane Hookom Microsoft Access MVP ---------- > Hello Duane > [quoted text clipped - 208 lines] > > > > > > > > > > > > > > > > > > > > > > Buddy Kadri >I agree with Dave. My suspicion is your calculations are being performed in > code within the On Format event of the report. If so, the results may vary > based on users having different default printers. And possibly on users paging back and forth in Preview mode, or just the Format even firing more than once for a Record (as is often the case). Event code is a decidedly risky place to perform calculations, especially cumulative calculations. Many of us were surprised by this phenomenon when we first encountered it. But it didn't take many occurrences to cure us of feeling the need to do so. Larry Linson Microsoft Office Access MVP Also an excellent point. Since I avoid phenoms where ever possible, My usual practice is to do the calculation in a control when possible, but if it becomes too complex for that, then a function called from the Control Source of a control. And that failing, refer the users to the Windows calculator <g> SignatureDave Hargis, Microsoft Access MVP > >I agree with Dave. My suspicion is your calculations are being performed in > > code within the On Format event of the report. If so, the results may vary [quoted text clipped - 10 lines] > Larry Linson > Microsoft Office Access MVP
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.