Help with calculations with an unspecified # of records

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

LynneMc - 30 Nov 2005 21:21 GMT

I am new to Access & trying to create a field that will calculate the
"balance due", by taking the "TotalTripCost" and subtracting all the
"PaymentAmount"s. I can get it to subtract the first payment, but I can't
figure out how to make it account for additional payments (there are varying
numbers of PaymentAmount s for any given TripID, all stored in separate
Payments records). Is there anything that will make it "add up all the
paymentamounts for this TripID and subtract from TotalTripCost"? This is what
I have now:

=[TotalTripCost]-DLookUp("[PaymentAmount]","[Payments]","[TripID] =
Forms![Trips]![TripID]")

Reply to this Message

Ed Warren - 30 Nov 2005 22:01 GMT

Look in Help under Dsum(), I think that is the function you want.

Ed Warren.

>I am new to Access & trying to create a field that will calculate the
> "balance due", by taking the "TotalTripCost" and subtracting all the
[quoted text clipped - 9 lines]
> =[TotalTripCost]-DLookUp("[PaymentAmount]","[Payments]","[TripID] =
> Forms![Trips]![TripID]")

Reply to this Message

LynneMc - 30 Nov 2005 22:38 GMT

Thank you Ed, that worked!

> Look in Help under Dsum(), I think that is the function you want.
>
[quoted text clipped - 13 lines]
> > =[TotalTripCost]-DLookUp("[PaymentAmount]","[Payments]","[TripID] =
> > Forms![Trips]![TripID]")

Reply to this Message