interest calculation

[jmd]

I need to write either a query or VB to calculate daily interest on a
simple savings account. I use the following formula(s) to calculate
the running balance for each transaction.

Transdate

Transaction

Bal: DSum("[transaction]*[transtype]","tblsavingstable","DatePart('m',
[transdate])<=" & [AMonth] & " And DatePart('d', [transdate])<=" &
[Aday] & "")

AMonth: DatePart("m",[TransDate])

ADay: DatePart("d",[transdate])

I need the interest to be calculated with or WITHOUT transactions.
I've attacked this problem by using queries, some VB, reports, etc.
with no success. Any help would be appreciated.

[Rich P]

What you do is to have 2 routines. One calculates with a transaction
and one calculates without a transaction. The trick will be in
determining if there was a transaction. If there is a transaction then
set a boolean var to true else the boolean var is false. If true then
calc with transaction. If false without transaction. If you are doing
the calcs from a table then create a new field for transactionYesNo. If
this field contains a yes... you can add that as part of your criteria
in the DSum formula
...
If...
Bal =
DSum("[transaction]*[transtype]","tblsavingstable","DatePart('m',
[transdate])<=" & [AMonth] & " And DatePart('d', [transdate])<=" &
[Aday] & " And TransactionYesNo = 'Yes'")
Else...
Bal =
DSum("[transaction]*[transtype]","tblsavingstable","DatePart('m',
[transdate])<=" & [AMonth] & " And DatePart('d', [transdate])<=" &
[Aday] & " And TransactionYesNo = 'No'")
End If
...
Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!