calculating total costs from 2 different fields

[wilkob]

I have a table with products and their costs per unit of measure.

some products are costed by weight and others by piece.

table looks something like this:

product
pcs
weight
unit of measure (EA or KGS)
cost

Query should look like this:

product
pcs
weight
Total Cost

Is it possible to write an expression that, depending on the unit of measure uses the pcs or weight field to calculate the total cost ??????

 

[RuralGuy]

TotalCost:=IIf([unit of measure] = "EA",[pcs]*[cost],[weight]*[cost])

 

[wilkob]

I get an error message when using this expression:

"The expression you entered contains an invalid syntax.

You omitted an operant or operator, you entered and invalid character or comma, or you entered text without surrounding it in quotation marks "

 

[RuralGuy]

What I gave you was for the QueryBuilder. Where did you put it?

If you put it in an SQL string it looks like:
IIf([unit of measure] = "EA",[pcs]*[cost],[weight]*[cost]) As TotalCost

 

[Len Boorman]

Perhaps you could change things a little
product
pcs remove this
weight becomes quantity
unit of measure (EA or KGS)
cost per Unit of measure

Total Cost calculated as Quantity * Cost per Unit of Measure

Quantity becomes say a Double and you only have to control that when UoM=EA the Quantity cannot have any decimal value

L

 

[wilkob]

found the solution


instead of using the commas I use a ; in the query builder and it worked??

anyway, thanks very much for all the leads