Totaling a calculated Field

[Argonaught]

I have a field in my database called [profit] this is a calculated field that works out the profit based on two other fields using an iif statement.
I now want to total my [profit] fields.
I created a new text box and inserted the following formula

=Sum([profit])
All i get is #error or #name
So i figure ive not enterd the name of the field correctly so i tried

=[profit]
which gives me the value of a single profit field, i figured this means im using the right field name, so im baffeled as to why i cant get the sum of all these fields.
Oh the control source on [profit] is =IIf([SoldFor]=0,0,[SoldFor]-[PaidFor]) if this is of any relevance i dont know.

Any help would be apreciated as im stumped

Cheers
Argonaught

 

[mderby]

I have tried that and it works.

Is the form with the Sum textbox bound to the TABLE (consist of field profit) ?

you can check this in the Form's Properties Record Source value.

hope this help.

mderby

 

[Fizzio]

Where do you have the control on the form - is it in the form footer?
Also - can there be null values in the profit - I see it is fed by an IIf statement but can [sold for] or [paid for] ever be null?

 

[Argonaught]

The Total Profit field is in the header.
The [profit] can be a null value as can both [paidfor] and [soldfor], the iif statement is there to stop me getting negative values.
I should explain that [profit] is not in a table it is a calculated field and so is [total profit], i think this may be where my problem is coming from.
When the [profit] field is viewed in design view it shows the formula rather than a field name, i tried to build an event and enter the formula that way but it just gave errors.

Im a newbie at this so im not too hot at it yet!

Any ideas?

 

[Rich]

=Sum([SoldFor]-[PaidFor])

 

[Argonaught]

i tried this but it, and it dose work but because the [soldfor] field is not always filled in it then deducts the [paidfor] field from the total profit giving an inacurate result.
this is why i total the [profit] using:
=IIf([SoldFor]=0,0,[SoldFor]-[PaidFor])

So to use =sum([Soldfor]-[paidfor]) bypasses the above formula.


Any ideas why =sum([profit]) doesnt work?

 

[Rich]

You can't Sum a calculated field,
I don't understand how you are getting "incorrect" results, if you bought something and haven't sold it then you're making a loss.
Sum(Iif(IsNull([SoldFor]),0,[SoldFor]-[PaidFor]))

 

[Fizzio]

You could try this
=Sum(IIf([SoldFor]=0,0,[SoldFor]-[PaidFor]))

Damn those quick fingers Rich!

 

[Argonaught]

Sorted! Cheers guys.
I understand your point about making a loss if you havent sold it, but its how they want it, so what can you do!

 

[Rich]

Damn those quick fingers Rich!

Not bad considering at least one of them's arthritic