Accounting project quesiton

[Veli Izzet]

Hi,

I have a table where I keep the transactions:

Table is :
TransactionID
TransactionValue
TransactionDate
Creditor
Debtor,

etc.

The Creditor and Debtors are Banks, Cash, Expenditures, or People, and
they may pay or get paid. (Like I transfer xxx amonut from Bank1 to
Bank2 one day and vice versa the next day)

What I want to do is be able to create reports about their standing
situations (the transactions too) for these.

I can create a query, where I can single out the Creditor/Debtor using:

stLinkCriteria = "[Borclu] =" & "'" & Me![Cari] & "'" & " OR " &
"[Alacakli] =" & "'" & Me![Cari] & "'"
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

I need to multiply the transactionValue by 1 if they are on the Creditor
(Alacakli) side, and multiply it by -1 if they are on the Debtor side,
and do a running total, and print it on the form.

Here I am stuck and need help..

Thanks for answers

 

[Mike Painter]

Hi,

I have a table where I keep the transactions:

Table is :
TransactionID
TransactionValue
TransactionDate
Creditor
Debtor,

etc.

Use one field for the ID of the entity and one to indicate if a credit or a
debit.

 

[Veli Izzet]

Mike,

Both the Creditor and debtor are in one record.. What is debt for one is
credit for the other, so I cannot and need not use another credit/debit
flag.

Anyhow, I solved the problem by using a query and multiplying the Value
by -1 or 1 depending on if the guy being reported about is creditor or
debtor in that situation.

 

[Wolfgang Kais]

Hello Veli.

The Creditor and Debtors are Banks, Cash, Expenditures, or People,
and they may pay or get paid. (Like I transfer xxx amonut from
Bank1 to Bank2 one day and vice versa the next day)

What I want to do is be able to create reports about their standing
situations (the transactions too) for these.

I can create a query, where I can single out the Creditor/Debtor using:

stLinkCriteria = "[Borclu] =" & "'" & Me![Cari] & "'" & " OR " &
"[Alacakli] =" & "'" & Me![Cari] & "'"
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

I need to multiply the transactionValue by 1 if they are on the Creditor
(Alacakli) side, and multiply it by -1 if they are on the Debtor side, and
do a running total, and print it on the form.

For the TransactionValue in your report, use something like:
=Iif([Alacakli]=Forms!NameOfForm!Cari,1,-1)*[TransactionValue]

For the sum in the report footer, you must repeat the formula, like:
=Sum(Iif([Alacakli]=Forms!NameOfForm!Cari,1,-1)*[TransactionValue])