Help with Building An Expression

[Guest]

Hello All,

I am dire need of making "if"/"then" type of calculation.
Example:

Fruit Type:
Net Weight:
Net Price:

We have at least 3 different types of fruit: Apples, Bananas & Pears
Apples sells for .5, Bananas .7 & Pears .3 per/pound

I need price to do something like:
If [Fruit Type] = "Apples" then [Net Weight]*.5
If [Fruit Type] = "Bananas" then [Net Weight]*.7
If [Fruit Type] = "Pears" then [Net Weight]*.3

How can I make this expression work. Any help would be WELCOMED!

 

[Marshall Barton]

I am dire need of making "if"/"then" type of calculation.
Example:

Fruit Type:
Net Weight:
Net Price:

We have at least 3 different types of fruit: Apples, Bananas & Pears
Apples sells for .5, Bananas .7 & Pears .3 per/pound

I need price to do something like:
If [Fruit Type] = "Apples" then [Net Weight]*.5
If [Fruit Type] = "Bananas" then [Net Weight]*.7
If [Fruit Type] = "Pears" then [Net Weight]*.3

How can I make this expression work. Any help would be WELCOMED!

You are way off track with that approach. Instead you
should have a table with the price for each type of fruit.
Then you would use a query to put the information together
and calculate the total price.

In very simple situations, you might even have the price in
the Fruits table. For example:

Fruit Price
Apples .5
Bananas .7
Pears .3

Then your calculation would simply be:
[Net Price] =[Net Weight]*Price

 

[Guest]

I have already tried that option and submitted my program prototype.
Management said they would rather have it this particular way and I wasn't
totally sure that Access could do it.

Thank you so much for your help.

I am dire need of making "if"/"then" type of calculation.
Example:

Fruit Type:
Net Weight:
Net Price:

We have at least 3 different types of fruit: Apples, Bananas & Pears
Apples sells for .5, Bananas .7 & Pears .3 per/pound

I need price to do something like:
If [Fruit Type] = "Apples" then [Net Weight]*.5
If [Fruit Type] = "Bananas" then [Net Weight]*.7
If [Fruit Type] = "Pears" then [Net Weight]*.3

How can I make this expression work. Any help would be WELCOMED!

You are way off track with that approach. Instead you
should have a table with the price for each type of fruit.
Then you would use a query to put the information together
and calculate the total price.

In very simple situations, you might even have the price in
the Fruits table. For example:

Fruit Price
Apples .5
Bananas .7
Pears .3

Then your calculation would simply be:
[Net Price] =[Net Weight]*Price

 

[Marshall Barton]

Well, Access can do it, but your management is speaking from
total ignorance. What are you going to do when the price
changes? Reprogram your applocation? It would be way, way
better to just edit a field in a table.

The expression you are asking for is really messy, making it
hard to understand and difficult to modify:

=IIf([Fruit Type] = "Apples", .5, IIf([Fruit Type] =
"Bananas", [Net Weight]*.7, IIf([Fruit Type] = "Pears", [Net
Weight]*.3, "invalid fruit")))

Heaven help you if you ever have to deal with more types of
fruit.
--
Marsh
MVP [MS Access]

I have already tried that option and submitted my program prototype.
Management said they would rather have it this particular way and I wasn't
totally sure that Access could do it.

Thank you so much for your help.

I am dire need of making "if"/"then" type of calculation.
Example:

Fruit Type:
Net Weight:
Net Price:

We have at least 3 different types of fruit: Apples, Bananas & Pears
Apples sells for .5, Bananas .7 & Pears .3 per/pound

I need price to do something like:
If [Fruit Type] = "Apples" then [Net Weight]*.5
If [Fruit Type] = "Bananas" then [Net Weight]*.7
If [Fruit Type] = "Pears" then [Net Weight]*.3

How can I make this expression work. Any help would be WELCOMED!

You are way off track with that approach. Instead you
should have a table with the price for each type of fruit.
Then you would use a query to put the information together
and calculate the total price.

In very simple situations, you might even have the price in
the Fruits table. For example:

Fruit Price
Apples .5
Bananas .7
Pears .3

Then your calculation would simply be:
[Net Price] =[Net Weight]*Price

 

[Guest]

Tina - where in your data base is this action required? In a form or query?