Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Query to Calculate a late payment for each 30 days late

0 views
Skip to first unread message

MMJII

unread,
Dec 27, 2003, 4:42:00 PM12/27/03
to
Hello all,
Fist Happy holidays to all!
Next
I have a query that calcs a late charge if the datefinished is greater than
or = 30 days.
My problem is I need to figure out a way to perform this calc for each 30
days that the payment is late.
Any ideas are greatly appreciated.

=IIf(Now()-[datefinished]<30 And [amount
due]>0,0,(([Subtotal])*1.5)/100)+[Sales Tax]

TIA
MMJII


Ken Snell

unread,
Dec 27, 2003, 5:11:07 PM12/27/03
to
What is the amount of the late fee per month?

You likely can calculate a late fee using an expression similar to this
(assuming that the late fee is 1.5% of the amount due (without including
previous late fees) each month):

=[Subtotal]*(0.015*(DateDiff("d", [datefinished], Now()) \ 30))


--
Ken Snell
<MS ACCESS MVP>

"MMJII" <macjo...@angelojohnson.com> wrote in message
news:exn4bJMz...@TK2MSFTNGP10.phx.gbl...

MMJII

unread,
Dec 29, 2003, 3:28:32 PM12/29/03
to
Ken,

Thank you for your time & knowledge.

The late fee is 1.5% of the outstanding balance including previous late
fees.
every thirty days.
example

1/1/04 - Bal due 100.00
2/1/04 - Bal due 100.00 x 1.5% = 101.50
3/1/04 - Bal due 101.50 x 1.5% = 103.02 etc...
MMJ II
"Ken Snell" <kthiss...@notcomcast.realnet> wrote in message
news:OofYaZMz...@tk2msftngp13.phx.gbl...

Ken Snell

unread,
Dec 29, 2003, 7:44:32 PM12/29/03
to
In that case, this should work:

LateFee = [Subtotal]*(1.015^(DateDiff("d", [datefinished], Now()) \ 30) - 1)

--
Ken Snell
<MS ACCESS MVP>

"MMJII" <macjo...@angelojohnson.com> wrote in message

news:uTs6upkz...@tk2msftngp13.phx.gbl...

John Spencer (MVP)

unread,
Dec 29, 2003, 8:00:26 PM12/29/03
to
Well, the math to do this is
balance due times
1 plus the percentage raised to the power of the number of periods involved.

BalanceDue * (1.015 ^ (DaysOverdue/30))

SO, if you want it to use entire 30 day periods only

(DateDiff("d",DateFinished,Date())\30)

Subtotal * (1.015^(DateDiff("d",DateFinished,Date())\30))

MMJII

unread,
Dec 30, 2003, 2:54:20 PM12/30/03
to
John, and Ken
Many thanks for the math, and query lessons.

It is very much appreciated.

Have a Happy New Year !!!!
MMJ II


"John Spencer (MVP)" <spen...@comcast.net> wrote in message
news:3FF0CE29...@comcast.net...

0 new messages