Why won't this Function Work?

[PNGBill]

I have a Function (it works by itself) that can return the Balance at a given date.

I want to use that function against dates in a table to determine if at that point in time the Loan Account was overdue and should be charged Late Fees.

Am really a bit confused and the function not working didn't help me get past stage one.

I have the start date for the loop and the future dates are plus 14 days until the Total to repay (another function) is zero.

I will try and nut out the start of the process and then fine tune the code as it builds.

So far my work today has gone nowhere.

Will try and post something soon.

 

[PNGBill]

Should this be a Function or A Sub??

I want to click a button on a form and get a value to charge as Late Fees returned in a Text Box Control on the Form.

The form will have the Loan Number [LDPK] as a textboc control source and either DLookup or sql in the code can provide the other key variables.

StartDate
Fortnightly Repay Amount
Late Fee Amount - for each fortnight late
and functions can provide ( I trust) the Loan Current Balance as at any of the Loop Dates and also the balance of Late Fees already charged and the Total to Pay.

This mix of variables along with the loop stooping each 14 days to recalculate, should be able to increment a new variable being the Late Fees that should be charged.

Then, deduct from this amount any Late Fees already charged (function) and Wala!! we have the amount that should be displayed in the Form Text box control as the Late Fee to now charge.

I think I can nut out most of this once I
A) work out the basic structure of the code and
B) get the functions to work within the code.

To arrive at a Current Balance, by sql, will really make the code pretty big which is why I preferred the function to be used.

This process is not done "Globally" ie for all records. It is selectively done so any slowness caused by using the functions may not be an issue.

Any suggestions on the starting point?? Sub or Function??

Appreciate any guidance

 

[jal]

Thanks jal,
trying to run in immediate window is just for test purposes.

Really trying to make a Do While Loop but so far hardly past stage 1

Okay, I understand you are just testing but have you also tried the test function WITHOUT the immediate window? Some compilers have more bugs in the immediate window than in the main window. Call the test function from a regular sub - it won't solve this thread but might allow you to make some progress.

 

[boblarson]

Okay, next part -

If you have defined this Private Function INSIDE a FORM module, and try to run it from the Immediate Window, It will give you a Sub or Function Undefined error (I just tested).

 

[MarkK]

If you have a form named YourForm, you can return an instance of that form immediately using Form_YourForm. So if that form has a public function that returns a value you can run it in the immediate window using ....

? Form_YourForm.YourFunction

Any member of a class module is only ever accessible via an instance of the class.

 

[boblarson]

If you have a form named YourForm, you can return an instance of that form immediately using Form_YourForm. So if that form has a public function that returns a value you can run it in the immediate window using ....

? Form_YourForm.YourFunction

Any member of a class module is only ever accessible via an instance of the class.

And that is why his stuff wouldn't run in the immediate window. Gotta love it.

 

[PNGBill]

So I guess the wash up here is that just because the immediate window fails doesn't always mean the function is in error.

I have messed around with my function so much I think the best way is to proceed with the main task.

Have started a Public Function in a General Module to do the task of Calculating the Late Fees.

Copied a small loop function from Allen Browne and will keep working on this.

Appreciate the advice and support

 

[PNGBill]

Looks like the issue was to do with my attempt to use a Module attached to a form rather then a general module.

Here is my completed and working Code to return a Late Fee value after looping through fortnights and using some functions to arrive at a final figure.

Appreciate critique which may well include advice on how to cut the code in half

Public Function LatefeesToBeCharged(LoanID As String) As Currency    'Calculate Late Fees for selected LoanID
    'Thanks To Allen Browne's Web Site for the start of this Code
   
    Dim rs As DAO.Recordset
    Dim sqlString As String
    Dim RepayFreq As String         ' Repayment Fortnightly, Monthly or Weekly
    Dim CurrentBalance As Currency  ' Balance As At each 14 days from the Start Date of the Loan plus 14 days
    Dim LateFeeRate As Currency     ' Kina Amount of Late Fee to be charged each fortnight
    Dim RepayTotal As Currency      ' Kina amount of accumulated Repayments up to Given Date
    Dim RepayPrev As Currency       ' Kina Amount Repaid Up To Last Fortnight
    Dim RepayAmount As Currency     ' Kina Amount Due to be Repaid Each Fortnight
    Dim CommenceDate As Date        ' Loan Commencement Date - Start Date plus 14 days
    Dim RepayCount As Integer       ' Count of Fortnights as Function Loops - Increases by 1 each loop
    Dim RepayNum As Integer         ' Number of Repayments agreed to for Loan
    Dim LateFeeDue As Currency      ' Late Fee now being calculated
    Dim LateFeesCharged As Currency ' Kina Amount of Late fees charged as at given date
    Dim LoanTotalOwing As Currency  ' Kina Amount of Loan to Pay
    
    RepayCount = 1          'Set Variable to start at 1
    LateFeeDue = 0          'Set variable to Zero
  
    sqlString = "SELECT TBLLOAN.LDPK, TBLLOAN.LDSt AS StartDate " & _
        "FROM TBLLOAN " & _
        "WHERE (((TBLLOAN.LDPK)=" & LoanID & "));"
        
    Set rs = DBEngine(0)(0).OpenRecordset(sqlString)
    
    CommenceDate = rs!StartDate + 14
  
    sqlString = "SELECT TBLLOAN.LDPK, TBLLOAN.LDPayK AS LDRepayK " & _
        "FROM TBLLOAN " & _
        "WHERE (((TBLLOAN.LDPK)=" & LoanID & "));"
        
    Set rs = DBEngine(0)(0).OpenRecordset(sqlString)
    
    RepayAmount = rs!LDRepayK
    
    sqlString = "SELECT TBLLOAN.LDPK, TBLLOAN.LDPayNo AS RepayNo " & _
        "FROM TBLLOAN " & _
        "WHERE (((TBLLOAN.LDPK)=" & LoanID & "));"
        
    Set rs = DBEngine(0)(0).OpenRecordset(sqlString)
    
    RepayNum = rs!RepayNo
    
    sqlString = "SELECT TBLLOAN.LDPK, TBLLOAN.LDPayFre AS LDRepayFreq " & _
        "FROM TBLLOAN " & _
        "WHERE (((TBLLOAN.LDPK)=" & LoanID & "));"
        
    Set rs = DBEngine(0)(0).OpenRecordset(sqlString)
    
    RepayFreq = rs!LDRepayFreq
    
    sqlString = "SELECT TBLLOAN.LDPK, TBLLOAN.LoanLateFee AS LateFeeKina " & _
        "FROM TBLLOAN " & _
        "WHERE (((TBLLOAN.LDPK)=" & LoanID & "));"
        
    Set rs = DBEngine(0)(0).OpenRecordset(sqlString)
    
    LateFeeRate = rs!LateFeeKina
    
    If RepayFreq = "Fortnightly" Then
    
    Do Until Date <= CommenceDate
    
        CurrentBalance = LoanBalanceToDate(LoanID, CommenceDate)                    'Function to Get Loan Balance as at given date
        LateFeesCharged = LateFeesToDate(LoanID, CommenceDate)                      'Function to Get Late Fees Charged as at given date
        RepayTotal = LoanRepaymentToDate(LoanID, CommenceDate)                      'Function to Get Repayments To Date as at given date
        LoanTotalOwing = LoanTotalToPay(LoanID)                                     'Function to Get Total Amount Yet to Pay on Loan
       
        If RepayCount = 1 Then                                                      'If First Repayment Due
            If (CurrentBalance - LateFeesCharged) > (RepayAmount / 2) Then              'If Repament Made for less then Half of Repayment Amount
                LateFeeDue = (LateFeeRate * 2)                                        'Charge double Late Fees
            Else
                LateFeeDue = LateFeeDue                                             'No Late Fees to Charge
            End If
            RepayPrev = RepayTotal                                                      'Set Repay Value For up to First Fortnight
        ElseIf RepayCount > 1 Then                                                  'If not First Repayment
                If RepayCount <= RepayNum Then                                      'And is Last Repay Date or earlier
                    If (RepayTotal - RepayPrev) < (RepayAmount / 2) Then            'Compare Any Recent Repayment to at least half the Agreed Repayment
                        LateFeeDue = LateFeeDue + LateFeeRate                       'Charge Late Fee
                    Else
                        LateFeeDue = LateFeeDue                                     'No Late fees Due
                    End If
                    RepayPrev = RepayTotal
                Else
                    If (CurrentBalance - LateFeesCharged) > 5 Then                  'If Loan Still Owing over K5.00 and Past Last Repay Date
                        LateFeeDue = LateFeeDue + LateFeeRate                       'Charge Late Fee
                    ElseIf (LoanTotalOwing - LateFeesCharged) < 6 Then              'Check If Loan fully repaid
                        CommenceDate = Date                                         'Loan Completed - set Commence Date to today's date to stop loop
                        
                    Else
                        LateFeeDue = LateFeeDue                                     'No Late fee Due
                    End If
                End If
               
           End If
           
        CommenceDate = CommenceDate + 14                                            'Add 14 Days to get Next loop
        RepayCount = RepayCount + 1                                                 'Add 1 to RepayCount variable for next loop
        
    Loop
        LateFeesCharged = LateFeesToDate(LoanID, CommenceDate)                      'Refresh Late Fees Charged as at Today's date
        LateFeeDue = LateFeeDue - LateFeesCharged                                   'Deduct any Late Fees already Charged
        LatefeesToBeCharged = LateFeeDue                                            'Return Result to Function
    
    Else
        MsgBox "Loan Repayments Not Fortnightly - Calculate Late Fees Some other way"            'Calculator won't work for Weekly or Monthly repayments
    End If
    
    rs.Close
    Set rs = Nothing
End Function