Calculation Trouble

;vv;vhj

"RacheleP" a écrit dans le message de
groupe de discussion : ...
I changed the expression to:

=NZ([LbsScrapVaue],(([Length]/12)*[WeightPerFoot])*[PcsScrapValue])

When I enter a number into the LbsScrapValue field everything works just
fine. When I enter a number into the PcsScrapValue field I get "#Error"
in
the LbsScrapValue field. (All fields are number fields) The error says
"Invalid Control Property: Control Source, Circular Reference" What did I
do
wrong?

Thanks,
~Rachele

"John W. Vinson" wrote:

On Fri, 26 Feb 2010 11:41:01 -0800, RacheleP
wrote:

John,

What I have created is a database to control our returned goods
authorizations. The process starts with issuing an RGA to a customer
for
material. That material can be quantified in terms of pounds or pieces,
depending on the customer. My tables simply store either the pieces or
pounds (in separate fields) being returned but my end report needs to
state
total pounds regardless of how the customer reported it. Thus the
calculation from pieces to pounds. I abandond storing the converted
data
based on your suggestion from your first post. What Im trying to do is
in my
report, if there is no value in the LbsScrapValue field it converts the
PcsScrapValue field to pounds (and displays in the LbsScrapValue field.
But
if there is a value in the LbsScrapValue field it displays that value.

When I use the formula below everything works fine if Im just converting
piece to pounds.
=(([Length] / 12) * [WeightPerFoot]) * [PcsScrapValue])

But what I want to add is a condition that says if there is a value in
the
LbsScrapValue field to display that value but if there is NO value in
that
field then to do the calculation from the PcsScrapValue field converting
it
to pounds. I thought this was how to write that conditional expression
but
its not returning correct values.

= [LbsScrapValue] Or IIf ([LbsScrapValue] = "0", (([Length] / 12) *
[WeightPerFoot])* [PcsScrapValue])

The OR operator doesn't do what you are assuming. It's a Boolean Algebra
operator, just as + or - are arithmatic operators. In Boolean Algebra,
the
following definitions apply:

True AND True = True
True AND False = False
False AND True = False
False AND False = False
True OR True = True
True OR False = True
False OR True = True
False OR False = False

That is, AND returns true if and only if both its arguments are TRUE; OR
returns true if either argument is true.

What I think you want is

NZ([LbsScrapValue], (([Length] / 12) * [WeightPerFoot])* [PcsScrapValue])

This will return LbsScrapValue if there is something in that field, and
will
return the value of the expression if there isn't. Note that if the field
is
of Number datatype you do *NOT* want the quotemarks (Access will have to
do an
extra step to convert from String to Number), and in any case, if the
field is
NULL then it is not equal to either 0 or to "0".

--

John W. Vinson [MVP]
.