Referencing a text field

[colmtourque]

I have a formula in the detail section of a report recshipping, this formula calculates the total shipping if it is within certain paramiters, it is hidden for certain individuals and not for others. In the level and report footer I am attempting to Sum up this value. But when I do access drops up the box asking for a value like it does not recognize the field. Is there a way to work around this.

 

[Rob.Mills]

This usually happens if the references are incorrect. If it is pulling data from textboxes, make sure that you're referencing the name of the box and not just the controlsource of the box. That's gotten me stumped before.

 

[colmtourque]

I used the expression builder so I do not think that is it.

 

[Rich]

You cannot sum calculated field directly, repeat the calculation
ie Sum([Amount]*[Quantity])

 

[colmtourque]

Rich I tried that but when I run the report it jumps back to edit mode with that field highlighted (Access's way of saying "um, NO!")
Here is the sum query:
=Sum(IIf(IIf([Expr1012]>181,[Printing And Copies],IIf([Expr1012]>91 Or [Expr1012]<181,[Printing And Copies]-15,IIf([Expr1012]<91,[Printing And Copies]-255,0)))<0,0,IIf([Expr1012]>181,[Printing And Copies],IIf([Expr1012]>91 Or [Expr1012]<181,[Printing And Copies]-15,IIf([Expr1012]<91,[Printing And Copies]-255,0)))))

Is there a chance that this is just asking to much?

I tried simplifying the expression some and it is giving me an answer to I am willing to bet that is it.

OK, this is now going to sound really sad. Does anyone see a better way to do the expression above.

What I am trying to say is
If someone has been with us for over 181 days they pay the total amount due, if they are from 91-180 days they pay all but 15 and if they are 90 and under they pay all but 25.
In looking at the if statement above I am wondering if it is just to complex.

 

[colmtourque]

[Expr1012]>91 Or [Expr1012]<181

Use "And" perhaps?
DOH!
Thanks, but expression builder will still not take the expression.
Is there anyway to say that if an answer comes out be less that 0 then use zero but otherwise put the answer to a formula? That is why the formula is so long.

I found though that the If > 181 expression is really extranious. If you think about it the options are >91 AND <181 or <91 so when I removed that the SUM worked. Thanks for all of the help Pat especially for pointing out the blad logic flow there. So when are you going to write that book?