If Then Math

[Tuckejam]

I seem to have backed myself into a bit of a corner and Need some help with "IF THEN" math, please



In the image below I have tried to Show the Form, Table, and Query that I'm working with. (and I'm not sure if this math belongs in the qry or the form??)

I am tracking Fines and need to total the amount of fines paid using the "Date Paid" field as the criteria.

something like

= IF ("DatePaid"= Value) then (sum "FineAmount")

Thanks for the help

[moke123]

try something like

=DSum("FineAmount","tblFines","TransCode = " & forms!...... & " and DatePaid is not null")

What happens with a partial payment?

[Gicu]

Hi James,

In the (totals) query you could use PaidFine:= Sum(IIF (Not Isnull([DatePaid]), [FineAmount],0))

Cheers,
Vlad

[CJ_London]

or perhaps

sum((isnull([date paid])+1)*fineAmount)

[ssanfu]

In tbl_Fines, you have a field for "FineAmount" but you do not have a field for "FinePaidAmount".
As Moke123 pointed out, there could be partial payments. So you need a field for Fine Paid Amount.

Or you could use the "FineAmount" field, but add aouther field for "Fine" or "Payment". This means redesigning the tbl_Fines table

[Tuckejam]

I know adding a "PaidAmount" wound more easily solve the problem, however as I said I kind of backed myself into a corner and would also need to redesign a bunch of qrys forms and reports to support a "paidamount"

the reason I got here is because in the last 3 years no one has ever made a partial fine payment, well.... actually in the last 3 years no on has every actually paid a fine.
the group votes to fine the chapters that didnt bring their paperwork or pay their dues on time. and then after a few months when everyone has racked up a bunch of fines they vote to waive all the fines and start over.

honestly herding cats would be easier