doing calculations using Query Vs Report using Access

[BuddyKadri]

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

 

[Klatuu]

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.

 

[Duane Hookom]

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.

 

[Klatuu]

Good insight, Duane. I didn't think about that.

 

[BuddyKadri]

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

 

[Klatuu]

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.

 

[BuddyKadri]

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

 

[Klatuu]

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.

 

[Larry Linson]

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

 

[Klatuu]

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>

 

[BuddyKadri]

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

 

[Duane Hookom]

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.

 

[BuddyKadri]

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 ) 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

 

[BuddyKadri]

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

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 ) 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.

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.