Advice on comparing Dates please

[PNGBill]

Hi Forum,
I have Function that calculates Late Fees where a payment isn't rec'd on a given Date (Loops every 14 days)
This works fine but sometimes we want to ignore a Date as the funds were rec'd soon after that date and the delay was not the fault of the Lendee.

I have setup a Form that displays the data and has a button to add a selected date to a Table with fields LoanID and IgnoreDate.
This works fine.

I then made a Public Function to return either the date or 1st Jan 1995 as the Variable SkipThisDate in the Loop of the Late Fee Calculate Code.
Extract of code is here.

 SkipThisDate = IgnoreLateFeeDate(LoanID, CommenceDate)  
        
        If CommenceDate = SkipThisDate Then
            CommenceDate = CommenceDate + 14   
            RepayCount = RepayCount + 1
        End If

The problem is that while the TblLateFeeIgnore has the correct records and dates match the code only ignores some of the dates and not all of them.

Is there some better way to compare dates then what I use above

If CommenceDate = SkipThisDate Then

Not sure where the problem is and would appreciate some advice or suggestions.

Thanks

 

[MarkK]

See if this thread helps ...
http://www.access-programmers.co.uk/forums/showthread.php?t=198123

 

[PNGBill]

Thanks lagbolt, will checkout using DateValue for comparing.

The main code has a start date and then add 14 days to that with each loop so CommenceDate is only in vba.

The IgnoreLateFeeDate(LoanID, CommenceDate) function uses an sql to find the date to compare. Maybe I should make this a DateValue rather then Date.

All dates are ShortDate so Time shouldn't be an issue.

I don't need to Compare a Date as such, just have a way to let vba know to continue the loop for this date.

I wonder if we should add a Flag field in our Table TBLTRANS where the Debit Entries are held on each fortnight and have the code check if the flag is 1 or 2 and act accordingly.
1 could be default and it is changed to a 2 when we select that date to be ignored.

This will save a new table to hold the Ignore data and just mean one additional field to an existing Table although this table holds the most records but a field with 1 numerical digit shouldn't cause our hdd to explode.

 

[MarkK]

1) "... so CommenceDate is only in vba."

2) "All dates are ShortDate so Time shouldn't be an issue."

3) "I don't need to Compare a Date as such..."

1) VBA dates also have a time component. If a time is present it will have an impact on how dates compare.

2) ShortDate is only a format. The underlying value may still have a time value.

3) This is a date comparison ...

If CommenceDate = SkipThisDate Then

Also, if you can determine the correct handling using existing fields, that is always preferable. If you introduce a flag that varies directly with data in the record then you break normalization rules.
Keep me posted,

 

[PNGBill]

Thanks lagbolt,
Not sure I understand this part 100%

Also, if you can determine the correct handling using existing fields, that is always preferable. If you introduce a flag that varies directly with data in the record then you break normalization rules.
Keep me posted,

The TblLateFeeIgnore is new and only has test data in it so can easily be deleted.

TBLTRANS, to add one new field "IgnoreLateFee" how would this upset normalisation rules? the value is 1 by default and we change that to 2 if we want to not charge a Late Fee for this Date.

All current operations will proceed as normal because the field will default 1 so it doesn't need to be Entered as such.
I can run an update query to populate existing records with 1 and then make the field Required so all new records will have 1.

In the Latefee vba code all it has to do is decide if the value is 1 or 2 and act accordingly which will be easier then the dates appear to be.

Maybe I am being lazy to resolve the date issue but I am also thinking this approach may be better as well.

 

[PNGBill]

Here is my code for IgnoreLateFeeDate(LoanID, Ignoredate) where I have added DateValue

  Dim dbs As DAO.Database, rst As DAO.Recordset
    Dim LateFeeDate As Date
    Dim SqlString As String
    
            'Sql to find if record exists in TblLatefeeIgnore on Given Date
    SqlString = "SELECT TblLateFeeIgnore.LoanID, TblLateFeeIgnore.IgnoreDate AS LateFeeIgnore " & _
        "FROM TblLateFeeIgnore " & _
        "WHERE (((TblLateFeeIgnore.LoanID)=" & LoanID & ") AND ((TblLateFeeIgnore.IgnoreDate)=#" & Format(IgnoreDate, "mm/dd/yyyy") & "#));"
        
              'Open Recordset
       Set dbs = CurrentDb()
       Set rst = dbs.OpenRecordset(SqlString)
       
        If rst.RecordCount <> 0 Then
        rst.MoveFirst
         'Assign SQL result to Variable
        LateFeeDate = NZ(rst!LateFeeIgnore, #1/1/1995#)
    Else
         LateFeeDate = #1/1/1995#       'If no record then use 1st Jan 1995 as date
    End If
    
   IgnoreLateFeeDate = DateValue(LateFeeDate)
    
    'Close database variables
    rst.Close
    dbs.Close
    
End Function

And here is the code extract from the main vba late fee code that deals with checking for a date.

 SkipThisDate = IgnoreLateFeeDate(LoanID, CommenceDate)                      'Function to check for matching record in TblLatefeeIgnore
        
        If CommenceDate = DateValue(SkipThisDate) Then
            CommenceDate = CommenceDate + 14                                        'Add 14 Days to CommenceDate if matching record in TblLatefeeIgnore
            RepayCount = RepayCount + 1                                             'Add 1 to RepayCount variable for next loop
        End If

I have the following records in TblLateFeeIgnore:
11th June, 9th July, 23rd July, 6th Aug, 17th Sept and 1st Oct - all 2010.

Out of these 6 records, 4 are working but the main code still wants to add a late fee for 23rd July and 1st Oct.

What should I look for with this situation?

US Date shouldn't be a factor as it would have caused an issue with 9th Jul and 6th Aug also.

Papua New Guinea, where I am, does have a rich tradition of Black Magic but until now it hasn't had any effect on MS Access, although just about everything else has

 

[PNGBill]

Just realised what may be an issue. I should Loop the Date Comparison as at the moment it checks the date and if matching, adds 14 days and continues.

This means that it will check this date and add 14 days and then process Late Fees for the next date without also checking that date -

 

[MarkK]

About the flag, I misunderstood your purpose. It totally makes sense to me to have a feature like this if you want to be able to manually override the late fee.
And apart from the date issues I don't really understand what you're working on. Couldn't you just subtract the due date from the actual return date and get a DaysOverdue value, and multiply that by a overdue rate?

days overdue = return date - due date
late fee = days overdue * daily overdue rate

Are late fees only charged on 14 day increments or something?
Also, how do you know the Black Magic is not affecting your database?
Cheers,

 

[PNGBill]

This small loop withing the main code appears to handle the issue.

SkipThisDate = IgnoreLateFeeDate(LoanID, CommenceDate)                      'Function to check for matching record in TblLatefeeIgnore
        Do While DateValue(CommenceDate) = DateValue(SkipThisDate)                  'Check for Matching Record for this Date
            CommenceDate = CommenceDate + 14                                        'Add 14 Days to get Next Commence date
            RepayCount = RepayCount + 1                                             'Add 1 to RepayCount variable to get Next Repay Count
            SkipThisDate = IgnoreLateFeeDate(LoanID, CommenceDate)                  'Reset SkipThisDate Value with new CommenceDate
        Loop

Thanks again lagbolt

 

[PNGBill]

Didn't notice your earlier post re why 14 days.
We lend personal loans mostly for 5, 10 or up to 20 fortnights and the repayments are supposed to be each fortnight.
TBLTRANS has the 5, 10 or 20 records loaded with the dates and amounts of each repayment at the time the loan is Issued.
A Late Fee is incurred when a repayment isn't made on or before the due date.
We don't charge Interest, except what is set at the the time of the loan so any Overdue amount causes a Late Fee to be charged.
I know it may be a little Different but it seems to work.

You will find banks now have Interest as a small part of their earnings. Fees appear to be the Go.

Getting the main code for the Late Fee was a little tricky but once I got most of the background work done by way of functions to obtain different Values as at different dates then the code made sence and appears to work.

Now, with the ability to "Ignore" a date, we have added an additional feature.

While Magic is prevalent unfortunately they never invented Crystal Balls which I certainly could have taken too - never mind, one may turn up eventually but probably too late by then

 

[MarkK]

OK. I was picturing late fees for videos or something.
But glad you got it worked out.
Crystal ball would be nice...

 

[gemma-the-husky]

i would put in some msgboxes and/or breakpoints in and step through the code


eg in this line

LateFeeDate = NZ(rst!LateFeeIgnore, #1/1/1995#)

it may not matter, but if latefeeignore is date 0, not null, it wont do what you think it should.


i dont quite understand this either.

IgnoreLateFeeDate = DateValue(LateFeeDate)

they are both dates, so surely the datavalue is not necessary.

to be honest, i am struggling to see exactly what your code is trying to do - so it may not be working exactly correctly.

 

[PNGBill]

The table only has records with dates.
Hopefully there will either be a record or no record will exist which then becomes 1st Jan 1995.


In one vba code we are incrementing a date by 14 days and checking to see if Late Fees should be charged as at that date.

In order to ignore a given Date, we have added a record to another Table with Fields LoanID and IgnoreDate.

A Functions returns A value using the main codes LoanID and CommenceDate from the Ignore table.
In the function if a date doesn't exist then 1st Jan 1995 will be returned.

This means there is always a record to compare. it is either the Exact date match or it is matching to 1st Jan 1995 which can never be an = match as there are no records with that date in our database.

probably over used DateValue due to the US date issue but this code works well now.

My problem was two fold. Matching the date and then processing the data.

lagbolt was able to satisfy me the date was no longer an issue and when I focused on the rest of the code, then everything worked out.

When you have something like this it is hard to know where the problem is. Date Issue or some other problem.

The main code is a little complex (to me anyway) which means sometimes a new feature added can have repercussions elsewhere.