-----Original Message-----
You left out the CCur(Round([Sum of Reg $]*0.85,2)), 0)
part of the expression. I can't get to work with that part
in the expression?
Thanks Dennis
a
-----Original Message-----
Sorry. I left off a closing paren. Try this:
Direct Reg $: Sum(IIf([Cost Center]='13601', [Sum of Reg
$]*0.85, 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)
John,
I tried to use the following expression:
Direct Reg $: Sum(IIf([Cost Center]='13601', CCur (Round
([Sum of Reg $]*0.85,2)), 0)
and recieved this error message:
The expression you entered is missing a closing
parenthesis, bracket (]) or vertical bar(|).
I tried adding and moving various parenthesis and
received
other errors. Can you review the expression and let me
know what may be wrong? My skills are not strong enough
to
figure this out.
Thanks,
Dennis
-----Original Message-----
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)
message
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)
message
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" <
[email protected]>
wrote in
message
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
.
.
.
.
.
