SumIf

[Tigereyes]

Hi everyone,
I am creating a reconciliations database. I have a rec form which has two subforms in it (both fed from a table holding all the transaction data), one showing all credits, one showing all debits. Both have a tick box against each record for me to check to "match" them off.
What I now need is a box either on each subform or on the main parent form which displays the current total of debit amt where matched box is ticked and another box showing current total of credit amt where matched box is ticked. I should then be able to take one from other and ensure I have no variance before submitting the match!!
Any ideas?

Thanking you very much in advance

 

[gemma-the-husky]

add a field in the table called "reconciled" or "selected"

put this in the query, and have another column for debit amount

=iif([reconciled],[debitamount],0])

and a similar one for credit amount

put all of these on your form.

now if you click the reconciled check box, the debit/credit amount wil be display, and you can sum these at the bottom of the form.

you probably need another flag called "processed", so when you are happy, you can update all the "reconciled" items as "processed"

----
not quite a sum if - you have to think in terms a manipulating a set of data to give you what you want.

 

[Tigereyes]

Thanks Gemma, my forms are not linked to a query though, they are linked straight to the table so the yes/no flag can be updated otherwise using queries makes it harder to update the record directly. I do not need to store this sum anywhere its just for an on screen display. I was hoping to use the Sumif function from Excel but that doesnt work in Access
Surely there must be a way of having a txt box on a form which shows something like =sum([credit amt] where [matched]=yes) ???

Its been a while since i wrote code so VERY rusty I'm afraid