# Change the formula in my query if the product is sold by unit or by weight

### Question

• Good morning,

I have a query named “Détails commande (étendu)” that I use to calculate the price of a product, the fields that I was using was:

Barcode - Réf produit – Quantité - Prix vente 1

The formula I was using was:

Prix total vente1: Round([PrixVente1]*[Quantité];2)

Now, my new customer is using a scale and a weight is retrieve from a Toledo scale, so my field “Weight” is retrieving this data:

01,500 for 1 kilo and ½

Now I have to change the formula according if this product is sold by unit or by weight, to determine that, I have a field named “UnitéVente” that shows 2 possibilities, “Kilos” or “Unité”

If the product is sold by “Unité”, then the formula

Round([PrixVente1]*[Quantité];2)

Would be fine

If the product is sold by “Kilos” then it would need to change calculation or using Iif or something similar, can someone suggest me what would be that formula to use in my query?

Thanks and have a great day

Claude Larocque

Wednesday, August 28, 2013 11:11 AM

• Do you have separate fields for price per unit and price per kg?

If not, you can use

Prix total vente1: Round([PrixVente1]*IIf([UnitéVente]="Kilos";[Weight];[Quantité]);2)

If you have a separate price per kg in a field named, say, PrixVente1kg, you can use

Prix total vente1: Round(IIf([UnitéVente]="Kilos";[PrixVente1kg]*[Weight];[PrixVente1]*[Quantité]);2)

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Wednesday, August 28, 2013 11:27 AM
• If the product is sold by “Kilos” then it would need to change calculation or using Iif or something similar, can someone suggest me what would be that formula to use in my query?

Hi Claude,

I made a comparable system. For that purpose I had 2 fields in the Product table: one for Unit, and one for Price_per_unit.
In the Order table I had a field Quantity, and a reference to the Product table.

The final price is always:   Round (Quantity * Price_per_unit, 2)

Your unit can be: kg, box, pair, ounces, etc. The Quantity is always expressed in the number of Units, that may be a decimal number. You can have different Price_per_unit's depending on the scale of the Unit, e.g. per 12-bottle-box versus per bottle, or per kilo versus per 100gr.

Imb.

Wednesday, August 28, 2013 11:53 AM

### All replies

• Do you have separate fields for price per unit and price per kg?

If not, you can use

Prix total vente1: Round([PrixVente1]*IIf([UnitéVente]="Kilos";[Weight];[Quantité]);2)

If you have a separate price per kg in a field named, say, PrixVente1kg, you can use

Prix total vente1: Round(IIf([UnitéVente]="Kilos";[PrixVente1kg]*[Weight];[PrixVente1]*[Quantité]);2)

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Wednesday, August 28, 2013 11:27 AM
• If the product is sold by “Kilos” then it would need to change calculation or using Iif or something similar, can someone suggest me what would be that formula to use in my query?

Hi Claude,

I made a comparable system. For that purpose I had 2 fields in the Product table: one for Unit, and one for Price_per_unit.
In the Order table I had a field Quantity, and a reference to the Product table.

The final price is always:   Round (Quantity * Price_per_unit, 2)

Your unit can be: kg, box, pair, ounces, etc. The Quantity is always expressed in the number of Units, that may be a decimal number. You can have different Price_per_unit's depending on the scale of the Unit, e.g. per 12-bottle-box versus per bottle, or per kilo versus per 100gr.

Imb.

Wednesday, August 28, 2013 11:53 AM
• Thanks Hans and Imb

Works both ways, I really appreciate the time you took to answer my question...

Have a great day!

Claude

Claude Larocque

Wednesday, August 28, 2013 4:40 PM