[AccessD] Problem With Ageing Function Further to.....

On 9 Feb 2006 at 7:47, Tom Keatley wrote:

> Thanks for the replies Stuart and Gustav.....
> 
> Of course you were right Stuart it WAS working ...After your reply I tried
> it again with one difference ...I put the date as a string (as you did)
> "01/01/2006" instead of a date #01/01/2006# and it worked fine .....I havent
> tried again as a date so I dont know whether that was a problem or maybe the
> moon was in the wrong plain or my coffee supply was too low . 

You never know what is going to happen when you use variants for 
everything. It can really turn around and bite you.

1. Make it a rule to explicitly type ALL variables.
2. It is much easier to keep track if you DIM all the variables in a Sub or 
Function at the start.
3. Using a naming convention so that you can tell a variables type from its 
name also  makes code a lot easier to follow/debug/modify a year later :-)

IN this case, you are using Year(D) and Month(D) multiple times, so for 
efficiency, I would calculate it once and store it in a variable

I would recommend you change your original code to be more like:

Function Ageing(Dte as Date) as Long
Dim dteD as Date
Dim dteCurr As Date
Dim dteDay30 As Date
Dim dteDay60 As Date
Dim dteOver As Date
Dimj lngYear as Long
Dim lngMonth as Long

lngYear = year(Date)
lngMonth = month(Date)

dteCurr = DateSerial(lngYear, lngMonth, 0)
dteDay30 = DateSerial(lngYear, lngMonth - 1, 1)
dteDay60 = DateSerial(lngYear, lngMonth - 2, 1)
dteOver = DateSerial(lngYear, lngMonth - 1, 0)

'Ageing function......
'Using Todays date as 15/04/2006 then
    '( 0)Current should be > 31/03/2006
    '(30)30Days              1/03/2006 to 31/03/2006
    '(60)60Days              1/02/2006 to 28/02/2006
    '(90)Over 60Days       < 1/02/2006

Select Case Dte
Case Is > dteCurr 'CURRENT AMOUNT
    Ageing = 0
Case dteDay30 To dteCurr ' 30 Days
    Ageing = 30
Case dteDay60 To dteOver ' 60 Days
    Ageing = 60
Case Is < dteOver
    Ageing = 90
Case Else
End Select

End Function

> 
> I am using the function in an update query to update the customers 
> transaction file aging wise on the fly as he is accessed, the result is then
> displayed in a list box on my customer form and also will be used in a
> statement report once I build it. The UNFORSEEN problem I now have is ......
> 
> Say a customer owes a total of $400 which has been incurred over the last 4
> months so current = $100 30day = $100 60day = $100 90day = $100
> 
> He now pays his account in total in the current month so....
> current = $-300
> 30day = $100
> 60day = $100
> 90day = $100
> 
> My transaction file is simply invoices as positive amounts and cheques and
> payments as negative amounts so that the total result is what is owing. How
> would you normally handle a situation like this. the result is actually
> accurate but of course this is not how you would display it.....
> 

To deal with short payments, payments over multiple periods etc, you need 
store "Balance Outstanding" against each Invoice. At the time you create 
the invoice, the balance outstanding is equal to the invoice amount.
When you process a payment, you need to credit invoices up to the amount of 
the payment, starting with the oldest invoice.  Then run your aging routine 
 on  Balance Outstanding only on invoices with a balance and ignore payment 
records.

-- 
Stuart