Yes Sir we did go around with this problem before.
I
have been working on this problem every spare monent
since. I have used the following round on every expression
and code that make a calculation. Maybe that is the
problem? Maybe I should only use the round on some
expressions and not all? The following are samples of
expressions used:
Rounded Expressions in one query:
Sum Of Pay: Round([AccentqryD]![Sum Of Pay],2)
Direct Pay $: Sum(IIf([Cost Center]="13601",Round([Sum Of
Pay]*0.85,2),Round([Sum Of Pay]*0,2)))
Indirect Pay: Sum(IIf([Cost Center]="13601",Round([Sum Of
Pay]*0.15,2),Round([Sum Of Pay],2)))
Sum of Reg $: Round([AccentqryD]![Sum of Reg $],2)
Direct Reg $: Sum(IIf([Cost Center]="13601",Round([Sum Of
Reg $]*0.85,2),Round([Sum Of Reg $]*0,2)))
Indirect Reg $: Sum(IIf([Cost Center]="13601",Round([Sum
Of Reg $]*0.15,2),Round([Sum Of Reg $],2)))
Sum Of Bnft $: Round([AccentqryD]![Sum Of Bnft Hours],2)
Direct Bnft $: Sum(IIf([Cost Center]="13601",Round([Sum Of
Bnft $]*0.85,2),Round([Sum Of Bnft $]*0,2)))
Indirect Bnft $: Sum(IIf([Cost Center]="13601",Round([Sum
Of Bnft $]*0.15,2),Round([Sum Of Bnft $],2)))
Sum Of Adjust $: Round([AccentqryD]![Sum Of Adjust $],2)
Direct Adjust $: Sum(IIf([Cost Center]="13601",Round([Sum
Of Adjust $]*0.85,2),Round([Sum Of Adjust $]*0,2)))
Indirect Adjust $: Sum(IIf([Cost Center]="13601",Round
([Sum Of Adjust $]*0.15,2),Round([Sum Of Adjust $],2)))
Expressions Not Rounded:
Indirect Hours: Sum(IIf([Cost Center]="13601",[Sum Of Reg
Hours ]*0.15,[Sum Of Reg Hours]*1))
Direct Hours: Sum(IIf([Cost Center]="13601",[Sum Of Reg
Hours ]*0.85,[Sum Of Reg Hours]*0))
Rounded Expressions in Reports:
This expression in report that sums all to a grand total:
=Sum(Round([Accentqry]![Sum Of Pay],2))
This expression in another report that gets the same
answer using a little different method:
=Sum(Round(([Employees].[Reg Hours]+[Employees].[Bnft
Hours])*([Employees].[Rate])+([Employees].[Adjust $]),2))
Code Expressions:
Private Sub Detail_Print(Cancel As Integer, PrintCount As
Integer)
If PrintCount = 1 Then curTotal = Round(curTotal + Nz
(Me.Workers_Pay, 0), 2)
End Sub
I hope this is enough information to give you an idea of
where I have gone wrong.
Thank you,
Dennis
-----Original Message-----
I think we went 'round with this before. <pun intended>
If the line items are the result of a calculation, you must round each value
internally and stuff it into a fixed point data type such as Currency or
Decimal. Then, add the fixed point results.
In the classic Northwind example, each line item is calculated as:
[UnitPrice]*[Quantity]*(1-[Discount])
Because Discount is a fraction, you sometimes end up with odd fractions of
cents. For example, a unit price of $35.10, quantity 35, and discount 25%
yields $921.37500 - and that odd half-cent has the potential to make your
total wrong. In the Access 2002 version of Northwind, the underlying query
uses a calculation that truncates the odd fractions of cents:
CCur([Order Details].UnitPrice*[Quantity]*(1- [Discount])/100)*100
Dividing by 100 and then converting to Currency yields $9.2137 (because
Currency has only 4 fixed decimal places). The final multiply by 100 yields
$921.3700 - no fractions.
You can also round by using a formula like:
CCur(Round([Order Details].[UnitPrice]*[Quantity]*(1- [Discount]),2))
This yields $921.3800 - again, no fractions.
To get the correct total, you must use one of the above expressions in a
SUM. If you want to round, then use the second expression and use:
=Sum(CCur(Round([Order Details].[UnitPrice]*[Quantity]*(1- [Discount]),2)))
... in your report or group footer.
--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
I asked this question before and I did get responses but
they did not solve the problem. The problem is, when I use
Excel or Access to total dollars, I cannot get Access to
match a calculator. Rounding changes the total but only by
one or two cents not the 22 cent difference.
When I use a page total, I cannot add the 35 or so numbers
on each page on a calculator to match either Excel's or
Access'total. The reason it must match the calculator is
money is transfered from one entity to another. As I
calculate the totals using software and the other entity
checks my work using a calculator, the totals do not match
and are off each month by cents. Of course this causes
accounting problems that cannot be reconciled. Currently,
to make it match, I add up the totals using a calculator
and type them on the reports generated by Access. Does
anyone have any ideas?
Dennis
.
