Calculating late fees on aging query

julie417

New member
Local time
Today, 05:33
Joined
Aug 24, 2013
Messages
7
Hi!
I've searched through the threads and can't find anything similar to my problem. Hope I'm not repeating :)

Am running a query based on Accounts Receivable table.
I want a field that will show late fees based on 30 days, 60 days and 90 days past due.

The fields I am working with are [InvoiceAmount] and [30-60-90] and [LateFee].

I want the LateFee field to show results for the calculations:
30-59 days = InvoiceAmount * .10
60-89 days = InvoiceAmount * .15
90 > = InvoiceAmount * .20
Current = Null

I hope this is enough information
Thanks!!
 
How do you calculate the 30 days, 60 days and 90 days?
 
Hi JHB

30_60_90: IIf(IsNull([datepaid]),IIf(DateDiff("d",[invoicedate],Date())<30,"Current",IIf(DateDiff("d",[invoicedate],Date()) Between 31 And 60,"30 Days",IIf(DateDiff("d",[invoicedate],Date()) Between 61 And 90,"60 Days","90 Days"))),"")
 
Hello julie417, I would create a Function to do this.. This function goes into a Standard Module..
Code:
Public Function getLateFee(invoiceDate, invoiceAmt As Double) As Double
[COLOR=Green]'********************
'Code Courtesy of
'  Paul Eugin
'********************[/COLOR]
    If IsDate(invoiceDate) Then
        Select Case DateDiff("d", invoiceDate, Date())
            Case 30 To 59
                getLateFee = invoiceAmt * 0.10
            Case 60 To 89
                getLateFee = invoiceAmt * 0.15
            Case Is > 90
                getLateFee = invoiceAmt * 0.20
            Case Else
                getLateFee = 0
        End Select
    Else
        getLateFee = 0
    End If
End Function
You can then Save the Module (if it is a New module, make sure you give it a name, something other than the name of the function), Compile the code.. Then go to the Query.. You do not need the column 30_60_90.. Just use the LateFee as..
Code:
LateFee: getLateFee([InvoiceDate], [InvoiceAmount])
 
Thanks pr2-eugin....I'm afraid I'm behind you on the learning curve. I haven't reached learning about modules. I can't get this to work (my fault). :o
 
Hi JHB

30_60_90: IIf(IsNull([datepaid]),IIf(DateDiff("d",[invoicedate],Date())<30,"Current",IIf(DateDiff("d",[invoicedate],Date()) Between 31 And 60,"30 Days",IIf(DateDiff("d",[invoicedate],Date()) Between 61 And 90,"60 Days","90 Days"))),"")
Does the above run ok? If you are getting errors then show the error number and the error description!
 
To be hones, I would advice against hard coding things like this as they tend to change.

I would make a table with columns StartDays, EndDays, Penalty
00 30 0
31 60 0.10
etc.

Then simply join the two tables to eachother in SQL view using
DateDiff("d",[invoicedate],Date())>=StartDays AND
DateDiff("d",[invoicedate],Date())<EndDays
 
julie417,

attachment.php


Compile the code, then use it in the Query..

PS: mailman's solution is also a good one..
 

Attachments

  • standardModule.png
    standardModule.png
    68 KB · Views: 1,727

Users who are viewing this thread

Back
Top Bottom