Re: Rounding?
From: John Viescas (JohnV_at_nomail.please)
Date: 03/06/04
- Next message: Gary Walter: "Re: Records Discrepancy"
- Previous message: kingtutt: "queries for deleting data"
- In reply to: Dennis: "Re: Rounding?"
- Next in thread: Dennis: "Re: Rounding?"
- Reply: Dennis: "Re: Rounding?"
- Reply: anonymous_at_discussions.microsoft.com: "Re: Rounding?"
- Messages sorted by: [ date ] [ thread ]
Date: Sat, 6 Mar 2004 07:43:52 -0600
Dennis-
The IIF function has three arguments:
1) The true/false test you want
2) The expression to evaluate if True
3) The expression to evaluate if False
Expressed in a sentence: If Cost Center is 13601, then multiply by .85,
else return zero.
Direct Reg $: Sum(IIf([Cost Center]='13601', [Sum of Reg $]*0.85, 0)
And to round:
Direct Reg $: Sum(IIf([Cost Center]='13601', CCur(Round([Sum of Reg
$]*0.85,2)), 0)
-- 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) "Dennis" <anonymous@discussions.microsoft.com> wrote in message news:521e01c40331$a70d6410$a601280a@phx.gbl... > I am using Access 2000. I tried to use the CCur(Round([Sum > of Reg $]*0.85, 2)) into my expression. > > Let me ask you about your first response: > > >> >Your massive IIF expression doesn't make sense. If > cost center is 13601,then you're returning [Sum of Reg $] > *0.85. Otherwise, you're returning zero - [Sum of Reg $] > * 0. Why not simplify it? > > How can I simplify it? All cost centers must show 0 and > only cost center 13601 should show 85% of the value in the > field. If the expression only includes cost center 13601 > will the other cost centers report as 0 if they are not > addressed? > > Like: Direct Reg $: Sum(IIf([Cost Center]='13601',CCur > (Round([Sum of Reg $] *0.85,2)))) ? > > Thanks Dennis > > > > > >-----Original Message----- > >Dennis- > > > >I would have to know more about how your queries and > tables are structured > >to give you a specific answer for all cases. In the > example I noted below, > >you would round (in Access 2000 and later) by doing: > > > >CCur(Round([Sum of Reg $]*0.85, 2)) > > > >You cannot use the Round function in Access 97. > > > >-- > >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) > ><anonymous@discussions.microsoft.com> wrote in message > >news:7d9301c402b5$608f0ba0$a101280a@phx.gbl... > >> I tried to use Rounding but could not make it work as it > >> includes other information like what number to round and > >> how many places? Not sure what that means. I need it to > >> round as a calculator world round. I tried many > different > >> formats but recieved error messages. I also tried round > up > >> with the same results. Could you help me by giving me a > >> couple examples in my expression? > >> > >> Thanks Dennis > >> > >> > >> >-----Original Message----- > >> >Dennis- > >> > > >> >Your massive IIF expression doesn't make sense. If > cost > >> center is 13601, > >> >then you're returning [Sum of Reg $]*0.85. Otherwise, > >> you're returning > >> >zero - [Sum of Reg $] * 0. Why not simplify it? > >> > > >> >But the problem is in the expression [Sum of Reg $] > *0.85 > >> > > >> >If [Sum of Reg $] contains pennies, then you'll get an > >> answer that contains > >> >odd fractions of cents. When displayed, Access will > >> round the value if you > >> >ask for just two decimal places, but this masks the > true > >> underlying value. > >> >When you add a tall column of such values, you're > likely > >> to be off several > >> >pennies in the total. You need to round such a > >> calculation to the nearest > >> >penny before you total it. Take a look at the Round > and > >> CCur (to convert > >> >the result back to currency) functions. > >> > > >> >Also, your second calculation will be off in fractions > of > >> cents if [Reg > >> >Hours] + [Bnft Hours] is a fraction of an hour and > [Rate] > >> + [Adjust $] is a > >> >fraction of a dollar. > >> > > >> >-- > >> >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) > >> >"Dennis" <anonymous@discussions.microsoft.com> wrote in > >> message > >> >news:480401c4025c$05385080$a601280a@phx.gbl... > >> >> I have an Access application that I use to calculate > >> >> figures. The problem I am experiencing is when my > >> central > >> >> office manually adds up the totals using a > calculator, > >> the > >> >> total by calculator is different by 15 to 25 cents > from > >> my > >> >> reports. I have exported the tables or queries into > >> excel > >> >> to check the totals and they match Access perfectly. > I > >> >> have discovered in Excel menu selection - Tools - > >> Options - > >> >> Calculations if I check the box Precision as > displayed, > >> >> the totals match the calculator perfectly. I believe > >> this > >> >> is a rounding difference between calculators and > >> Access. I > >> >> question I have is what do I need to do to Access to > >> have > >> >> the calculations match the calculator? I looked in > the > >> >> same menu selection as above and found nothing. Below > >> are > >> >> samples of expressions used in related queries: > >> >> > >> >> Direct Reg $: Sum(IIf([Cost Center]='13601',[Sum of > Reg > >> $] > >> >> *0.85,IIf([Cost Center]='26401',[Sum of Reg $]*0,IIf > >> ([Cost > >> >> Center]='26101',[Sum of Reg $]*0,IIf([Cost Center] > >> ='21101', > >> >> [Sum of Reg $]*0,IIf([Cost Center]='89200',[Sum of > Reg > >> $] > >> >> *0,IIf([Cost Center]='89100',[Sum of Reg $]*0,IIf > ([Cost > >> >> Center]='89600',[Sum of Reg $]*0)))))))) > >> >> > >> >> > >> >> Sum Of Pay: Sum(([Employees]![Reg Hours]+[Employees]! > >> [Bnft > >> >> Hours])*([Employees]![Rate])+([Employees]![Adjust > $])) > >> >> > >> >> > >> >> Thanks your help, > >> >> > >> >> Dennis > >> >> > >> >> > >> >> > >> > > >> > > >> >. > >> > > > > > > >. > >
- Next message: Gary Walter: "Re: Records Discrepancy"
- Previous message: kingtutt: "queries for deleting data"
- In reply to: Dennis: "Re: Rounding?"
- Next in thread: Dennis: "Re: Rounding?"
- Reply: Dennis: "Re: Rounding?"
- Reply: anonymous_at_discussions.microsoft.com: "Re: Rounding?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
