ACCESS iif statement won't work

[Pamela]

Trying to have expression calculate in field 3;

If field 4 =0 then field 1 divided by field 2, otherwise field one minus (field 4 multiplied by field 5) divided by field 2

otherwise answer should be
Field 1 =114 Field 2 =10 Field 4 =14 Field 5=1 then the calculation in field 3 should =10 114-(14)=(answer 100)/10 =10,

but the answer is coming out to 112.6

Here is my expression;

IIf([Truss O/C -2]=0,[Length]/[Truss O/C -1],[Length]-([Truss O/C -2]*[# of Bays - 2])/[Truss O/C -1])

[Pamela]

For clarification

Field 1 =Length
Field 2 =Truss O/C-1
Field 3 =#of Bays-1
Field 4 =Truss O/C-2
Field 5 =#of Bays-2

[Rabbit]

Here's some math 101.

In mathematics, multiplication and division is calculated before subtration and addition. To change the natural order, you need to group the appropriate calculations using parentheses. The natural order of calculation is as follows, from left to right:
Parentheses, exponents, multiplication and division, subtraction and addition.

What you wrote comes out to: 114 - (14 * 1) / 10
Using the rules of math above, the calcuation at each step is:

114 - (14 * 1) / 10
114 - 14 / 10
114 - 1.4
112.6

Therefore, if you want to calculate the subtration after the first multiplication and then the final division, you need to change the natural order with parentheses.

(114 - 14 * 1) / 10
(114 - 14) / 10
100 / 10
10

[Pamela]

Great that works, but now I'm trying to add an 3rd bay spacing & qty. and again formula messes up.

To explain better we sell buildings that have truss spacing and sometimes a building can have up to 3 bay spacing sizes, I need the [# of Bays-1] to auto calculate when there are 0's in bay/truss 2 or and when only bay 2 has a value and when both bay 2 and bay 3 have a value.

eg.

Building 126 ft long 10 bays @ 10 Truss o/c, one bay @ 12 truss o/c and one bay at 14 truss o/c

I managed to make this work but when I changed qty to have a zero in bay 2 or 3 it should have recalculated to show a decimal in bay 1, but it just remained at 10.

I am brand new to access... the expressions are not like they are in excel formulas as I have it working in excel but it does not calculate properly in access

[Rabbit]

Show us the formula. Obviously you've changed it. It's hard to tell you what is wrong in the formula when we can't see what you've changed.

[Pamela]

This works, IIf([Truss O/C -2]=0,[Length]/[Truss O/C -1],([Length]-[Truss O/C -2]*[# of Bays - 2])/10)

if I'm just using 1 or 2 bays but when I tried to repeat the otherwise formula for it to take into account 3rd bay it does not calculate properly.

[Rabbit]

That's pretty much the same formula from before... Where's the third bay that you were talking about. I don't see it anywhere in the formula.

[Pamela]

I am asking for help on how to create the formula with a 3rd bay, obviously the ones I have tried do not work.

[Pamela]

Rabbit, thank you for your help... but I signed up to this site as a HELP forum, I didn't come here for condescending remarks like "here's some math 101" If I knew how to create the expressions in Access I wouldn't be here.

If you truly want to help people try doing it without talking down to them.

[Rabbit]

I'm sorry if I sounded condescending, that was not my intent. Not everyone knows the order of operations in math and the way the formula was written pointed to a misunderstanding of the order of operations as the cause of why the formula didn't work the way you thought it would.

I was only trying to establish the rules that Access uses to evaluate a mathematical expression. The reason the expression in Access didn't work wasn't because of an Access issue, it was a mathematical issue. Which is why I explained the math. If it was an Access issue, I would have shared some Access 101 information so you understood what was going on in the expression.

As for the third bay, I wanted to see your attempts at implementing it so that I could explain how to fix it and why it works or doesn't work so that you can understand the root causes. Where as if I just gave you the answer, there's no knowledge gained and you'll be more likely to make the same mistake again.