Prev: Value list updates not appearing in form
Next: Date query
From: Jen on 2 Jun 2010 15:40


"Lord Kelvan" wrote:

> mmm well my guess would be
>
> [cost on hand]=iif(Nz([PieceCost])*Nz([PiecesOnHand])<>0,
> Nz([PieceCost])*Nz([PiecesOnHand]),iif(Nz([PieceCost])*Nz([PiecesOnHand])<>0,
> Nz([PieceCost])*Nz([PiecesOnHand]),Nz([UnitCost],0)*Nz([UnitsOnHand],
> 0)))
>
> This checks if the cost of pieces is 0 if not then it says the cost on
> hand is the cost of pieces if it is 0 then it checks the subunits if
> the sub units cost is not 0 it will use the subunits cost as cost on
> hand and if that is 0 it will use the units as cost on hand.
>
> Though this code won't work if you have pieces lets say and they have
> no cost to the company then I would use this code
>
> [cost on hand]=iif(Nz([Pieces on hand])<>0,
> Nz([PieceCost])*Nz([PiecesOnHand]),iif(Nz([Subunits on hand])<>0,
> Nz([PieceCost])*Nz([PiecesOnHand]),Nz([UnitCost],0)*Nz([UnitsOnHand],
> 0)))
>
> This will do the same but will check to see if the pieces on hand
> value is 0 and so on rather than the cost value
>
> Hope this helps
>
> Regards
> Kelvan
> .
>

I have to apologize, I managed to screw up which fields I needed to use to
perform the profit calculation.

I originally stated that I needed the calculation to use whichever of these
three fields was the last field to have a data entry:
Cost of all Units on hand , Cost of all Subunits on hand , or Cost of all
Pieces on hand

I also neglected to say that I was only trying to find the profit for a
single item, in which case I may notice my big mistake about the fields. In
basic math, the profit would be figured as follows:

PROFIT = [SellingAt]- the cost of a single item

The "cost of a single item" would be whichever of these three fields was the
LAST field to have a data entry:

[UnitCost], [SubUnitCost], or [PieceCost]

each of which are calculated based on the previous set of fields. Subunits
are based on units and pieces are based on subunits.

Typically the "cost of a single item" data will be in [PieceCost], but
occasionally it will be in one of the other two. Additionally, if the final
"cost of a single item" data is in the [PieceCost] field, there will also be
data in [UnitCost] and [SubUnitCost] fields that helped calculate the
[PieceCost]. If the final "cost of a single item" data is in the
[SubUnitCost] field, then there will also be data in the [UnitCost] field and
the [PieceCost] will be Null. If the final "cost of a single item" data is in
the [UnitCost] field, then the [SubUnitCost] and [PieceCost] fields will both
be Null.

Is there a calculated expression that I can use that will find the last of
the three calculated data fields and use that data to figure the profit?

If there isn't a way to do what I'm asking using calculated expressions, is
there a way I can set the [Profit] field to ask me which of the three field's
data I'd like to use? Or would it be easier to create a "cost of a single
item" field that would allow me to pull in the data from one of those three
fields so that the expression to calculate the profit in the [Profit] field
would be as simple as the basic math example above?

Again, I apologize for my initial confusion and thanks again in advance.
J
 | 
Pages: 1
Prev: Value list updates not appearing in form
Next: Date query