Adding Fields

[demon147]

I have a report and want to add an unbound text box to the report that adds 6 fields together with the highest total of 1 of 3 fields.

Help Appreciated.

 

[jtmach]

O.k. this is kind of messy so be prepared to stare at if for a few minutes. This is as simple as I know how to make it. It assumes that the three text boxes that you want to compare are named t1, t2, and t3. The six text boxes to add are t4, t5, t6, t7, t8, t9. Once you have all of the names modified put this in the control source of your unbound textbox

=IIf([t3]>IIf([t1]>[t2],[t1],[t2]),[t3],IIf([t1]>[t2],[t1],[t2]))+(t4+t5+t6+t7+t8+t9)

Have fun. The hardest questions always have the easiest answers.

 

[demon147]

I typed in the expression the way you have it but I get an error message saying syntax error.

Any Ideas,
Thanks

 

[JoeMiller]

I don't understand the question . .


you have six fields that you want to have the highest three added together?

you have six fields, the highest tells you what fields to add together?

you have six fields, you want to add between 1 and 3 of them together depending on the highest?


Can you explain a little further what you're trying to do? Maybe then I can come to an answer for you..

Joe Miller
joe.miller@flotech.net

 

[demon147]

I have 6 fields that are going te added together with the highest value of 1 of 3 fields.

Example F1+F2+F3+F4+F5+F6 and the highest value of either F7,F8 or F9.

Hope this explains better.

 

[JoeMiller]

AHHHH!! It's all so clear now!!

=(F1+F2+F3+F4+F5+F6)+(IIf([F7]>[F8],IIf([F7]>[F9],[F7],[F9]),IIf([F8]>[F9],[F8],[F9])))

HTH Joe Miller
joe.miller@flotech.net

 

[jtmach]

Ok sticking with your example it should be -

=IIf([f7]>IIf([f8]>[f9],[f8],[f9]),[f7],IIf([f8]>[f9],[f8],[f9]))+(f1+f2+f3+f4+f5+f6)

I have tested this in both Access 2000 and 97 it does work. It is compliciated, maybe you miskeyed. Make sure to keep it all one one line. The hardest questions always have the easiest answers.

 

[demon147]

Thanks for the help guys it all works fine now. I have been driving myself crazy for about a week trying to figure this out.

Thanks very much.

 

[demon147]

Joe Miller I used your example the only I need to do now is insert another textbox and use the same formula but I want to divide the answer by 7.

Help Appreciated.

 

[JoeMiller]

Put parenthesis around the whole function, then tack a divide by 7 at the end (/7):

=((F1+F2+F3+F4+F5+F6)+(IIf([F7]>[F8],IIf([F7]>[F9],[F7],[F9]),IIf([F8]>[F9],[F8],[F9]))))/7

OR!!

You can reference your other textbox with just the answer to the equation and divide it by 7:

=[MyTextBoxWithAnswer]/7

HTH Joe Miller
joe.miller@flotech.net