Error in Expression

[Johnrg]

Guys,

I have the following text box expression that is giving me an incorrect result.

As an example if I have 546 x 6.0 I should get 3276 but my expression is returning 3112.20

As another example I had 45 x 6.0 should have returned me 270 but it returned me 256.50

Can anyone see any problem with the code? I am a loss why its giving me incorrect results

PS: Formatting is set to "Standard"

=NZ([Original300]*0.3)+NZ([Original400]*0.4)+NZ([Original600]*0.6)+NZ([Original900]*0.9)+NZ([Original1090]*1.09)+NZ([Original1200]*1.2)+NZ([Original1500]*1.5)+NZ([Original1800]*1.8)+NZ([Original2100]*2.1)+NZ([Original2400]*2.4)+NZ([Original2700]*2.7)+NZ([Original3000]*3)+NZ([Original3300]*3.3)+NZ([Original3600]*3.6)+NZ([Original3900]*3.9)+NZ([Original4200]*4.2)+NZ([Original4500]*4.5)+NZ([Original4800]*4.8)+NZ([Original5100]*5.1)+NZ([Original5200]*5.2)+NZ([Original5400]*5.4)+NZ([Original5700]*5.7)+NZ([Original6000]*6)+NZ([Original6600]*6.6)+NZ([Original7200]*7.2)

 

[boblarson]

What is the datatype being used in the table?

 

[Johnrg]

Hi Bob,
Data type in record source table is set to "Number" - Long Interger
Thanks
JohnG

 

[Johnrg]

Anyone comment on why the expression may not be calculating correctlly?

 

[boblarson]

Try putting your NZ correctly around the fields:

=NZ([Original300])*0.3+NZ([Original400])*0.4+NZ([Original600])*0.6+NZ([Original900])*0.9+NZ([Original1090])*1.09+NZ([Original1200])*1.2+NZ([Original1500])*1.5+NZ([Original1800])*1.8+NZ([Original2100])*2.1+NZ([Original2400])*2.4+NZ([Original2700])*2.7+NZ([Original3000])*3+NZ([Original3300])*3.3+NZ([Original3600])*3.6+NZ([Original3900])*3.9+NZ([Original4200])*4.2+NZ([Original4500])*4.5+NZ([Original4800])*4.8+NZ([Original5100])*5.1+NZ([Original5200])*5.2+NZ([Original5400])*5.4+NZ([Original5700])*5.7+NZ([Original6000])*6+NZ([Original6600])*6.6+NZ([Original7200])*7.2

 

[Johnrg]

Hi Bob,

Thanks for that, no still same error.

I ran a couple of tests and it seems like (for some crazy reason) its looking at another feild before when caluclating.

Heres an example:

The data in the feild Original6000 is 65 .
The expression should return 65Xoriginal6000 = 390.
But its actually returning 370.5 which is 65 x original5700.

I checked the record source table and there is definattly no data in the original5700 table.

Any other ideas anyone?

JohnG

 

[Rich]

=NZ([Original300]*0.3)+NZ([Original400]*0.4)+NZ([Original600]*0.6)+NZ([Original900

Try
=(NZ([Original300]*0.3))+(NZ([Original400]*0.4))+(NZ([Original600]*0.6))+(NZ([Original900]))

 

[boblarson]

Try
=(NZ([Original300]*0.3))+(NZ([Original400]*0.4))+(NZ([Original600]*0.6))+(NZ([Original900]))

More like:

=(NZ([Original300])*0.3)+(NZ([Original400])*0.4)+(NZ([Original600])*0.6)+(NZ([Original900])*.9) etc...

 

[Johnrg]

Guys,
Got it to work at last.
The Expression was fine.
The error was two of the feilds had the same name so we double calculating.
Thanks for all your input.
JohnG

 

[boblarson]

Yep, that would be a problem. Glad you figured out the problem and got it working.