Why is this expression not calculating correctly?

[Rich_B]

The expression below is not returning the result I expect.

=IIf(IsNull([MATERIAL]),"Select Material",([Height]*[Width]*[Length]/1000000000)*[QUANTITY])

Height = 140
Width = 150
Length = 1200
Quantity = 5

The result should be 0.126 but it's returning 10.2858

Any help appreciated.

Thank you

 

[RuralGuy]

Try:
(([Height]*[Width]*[Length])/1000000000)*[QUANTITY])

 

[Minty]

The brackets don't cancel out and therefore this wouldn't work - so can you check that what you've typed is what is actually in your code ?

 

[Rich_B]

Try:
(([Height]*[Width]*[Length])/1000000000)*[QUANTITY])

Thank you for your input but I tried this and got the same result.

 

[cyanidem]

Hmm...
Your fields are:

Height
Weight
Length
Quantity

But in calculation its:

Height
Width
Length
Quantity

Weight....Width...

 

[Rich_B]

The brackets don't cancel out and therefore this wouldn't work - so can you check that what you've typed is what is actually in your code ?

Hi

The code is cut and pasted and the other specified values are definitely correct.

I assumed I had got the syntax wrong somewhere.

 

[Rich_B]

Hmm...
Your fields are:

Height
Weight
Length
Quantity

But in calculation its:

Height
Width
Length
Quantity

Weight....Width...

That was a typo, now edited but the problem remains.

Thank you

 

[arnelgp]

=IIf(IsNull([MATERIAL]),"Select Material",([Height]*[Width]*[Length]/1000000000)*[QUANTITY])

should be:

=IIf(IsNull([MATERIAL]),"Select Material",([Height]*[Weight]*[Length]/1000000000)*[QUANTITY])

 

[Minty]

Is this a form control source or in a query ?
What happens if you substitute hard coded values into the expression?

 

[Rich_B]

=IIf(IsNull([MATERIAL]),"Select Material",([Height]*[Width]*[Length]/1000000000)*[QUANTITY])

should be:

=IIf(IsNull([MATERIAL]),"Select Material",([Height]*[Weight]*[Length]/1000000000)*[QUANTITY])

Thank you for your input.

Both are actually width. I just made a typo in the original post.

I'm still not getting the right result though.

 

[Rich_B]

Is this a form control source or in a query ?
What happens if you substitute hard coded values into the expression?

It is a form control source.

I have substituted hard coded values and it works as expected.

 

[Minty]

I wonder if it's because Height , Width and Length are possibly reserved names. What if you call them something else ?

 

[cyanidem]

This is strange indeed, I made a query and a form based on table, entered your values and expression and... In query it works perfectly but on form I got result 9.70872.

 

[cyanidem]

Changing names didn't change the result at all, also changing form sizes didn't matter. My result is still 9.70872.
I'm curious why is it different than OP's though.

 

[arnelgp]

will using the name of textbox make difference. Me.theNameOfTextBox (not the control source).

 

[Rich_B]

I wonder if it's because Height , Width and Length are possibly reserved names. What if you call them something else ?

That solved it.

I changed the field names to H, W & L and it all works fine now.

Thanks Minty

 

[cyanidem]

Arnel wins. Referencing to the control name works, result is 0.126 now.
But why?