Math Handler

[jeffshex]

I need some advice or perhaps a better way of calculating.
I am trying to figure out the following:
I have a tblGeneral that lists out all these people and an Amount we said we'd pay them. I have a tblPayments that list out when a payment was made and for how much.
I then take Amount - Payments to find how much we have left to pay.

The problem arises when I have one person that we promised to pay $8000. We made one payment of $2000 and then had to reclaim the remaining $6000.
So the calculated field is setup to do [Amount]-[txtTotalPayments]
(txtTotalPayments is a sum on their payments.

So taking the $2000 paid and the -$6000 reclaimed = -$4000
And the calculated field is thinking $8000 - -$4000 = $12,000 still owed. Subtracting a negative causes problems.

I am kind of perplexed because of this one individual.
All I'm really wanting is it to say that out of the $8000 we paid $2000 and took back $6000, and now we don't owe any more.

Geeesh this is more complicated than I thought.

 

[traingamer]

Your [tblpayments] entry should be +$6000 with a type of 'reclaim' (or something else to flag it) not -$6000

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[jeffshex]

I know...but I guess the tblPayments is already an "assumed" negative number. That way anything that shows up as -$$$ would really be a positive.

I guess is there a way to add some vba in the report that works the same as in the form? Where as if a field is <0 than it just displays $0.00?

 

[jebry]

Hi!

I think that the easiest way would be like this in the control source of the text box or the field line of a query:

=IIf(Reclaimed < 0, 0, [Amount] - txtPayments)

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[jeffshex]

After some more thought...I'm thinking maybe I just need a reclaimed table or something of that nature.
That would probably be a better way to track that stuff I'm guessing.

Right?

 

[traingamer]

Quyite possibly.

 

[traingamer]

*Quite*

 

[TheAceMan1]

How are ya jeffshex . . .

How a combobox that displays Payment Or Reclaim as a descriptor! Then its a simple matter of adding that additional -6000 record?

Your thoughts?

See Ya! . . . . . .

 

[jeffshex]

I decided to go with a different table. That seems to be working well. I hit a snag though...]
I have a report that prints out everyone.
In that are 2 subreports: Payments and Reclaimed.
I need to list each indvidual's remaining amount, which is essentially [Amount]-[SumOfPayments]-[SumOfReclaimed].
My snag is though, I can't get the sum of payments or sum of reclaimed. I am thinking I need to do a Dsum in a text box, but am not sure how to run that.
Any ideas?

 

[TheAceMan1]

jeffshex . . .

[blue]So everyone can benefit from any resolution here[/blue] start a new post . . . and don't forget to include the particulars!

See Ya! . . . . . .