Sum

[Mike Hughes]

I have a form with the fields Amount and Deposit

I then have two hidden fields which gives me a running total of both
Amount =Sum([Amount]) and Deposit =Sum([Deposit])

I want to add a visible field to the top of the form which displays the current balance sum Deposit - sum Amount

I can't figure out the fomula for this field to give me the balance.

Would someone please help me?

Thanks MIke

 

[Beetle]

Have you tried;

=Sum([Deposit])-Sum([Amount])

 

[Mike Hughes]

It sounds like it would work, but it doesn't. Any other ideas?

Thanks MIke

 

[Beetle]

Yes, it should work.

What do you mean by "it doesn't"?

Are you getting #Error?

Something else?

Any results at all?

Where is the text box that you are putting this expression in?

 

[Mike Hughes]

No error message.

Just $0.00

 

[Mike Hughes]

I tried it in the Form Header, Form Footer and in the Detail section

 

[Beetle]

So it shows $0.00 for all records even though the other two text boxes with the separate Sum functions show values that should not result in $0.00?

Can you post a copy of your db with any sensitive info removed? (If so, post it in A2003 and older .mdb format - in case you're using A2007 or later)

 

[Mike Hughes]

Sure, thanks for giving this a look.

Mike

 

[phatus]

i look in your db i just change the formula in your unboundtextbox balance...

=[Amount]-[Deposit]

just put only this..

look the formula in you balance...

hope it help...

ps...dont put spaces in your field name... ^ ^

 

[Beetle]

Mike:

The problem is that you have the Data Entry property of the form set to Yes, so the form will not display, or be able to calculate, any existing records. Also, you don't really need the two hidden text boxes here. They are not needed for the running balance calculation, and since they are hidden they serve no other purpose.

Phatus:

i look in your db i just change the formula in your unboundtextbox balance...

Code:
=[Amount]-[Deposit]
just put only this..

look the formula in you balance...

Not quite. This will display the balance for the current record only. The OP wants a running balance of all records, so the Sum function must be used.

 

[Mike Hughes]

Bettle, you did it! Thanks !! Mike