Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Unanswered: A/R Aging in Access 2000 ...

    Hi all ...

    I'm working this out myself also ... Maybe you have some ideas on how BEST (and flexible re: spanning yearly boundaries) to implement this:

    I'm generating an A/R Aging report where I have an Estimated Date of Bill that is in the format of Month/Year. For this aging to be MOSTLY accurate the age need only be within the same month ...
    Example:

    Bill Due in 8/04
    Age: Current
    Reason: Less than 30 days

    Now here's what I'm trying to do: Bucketize ... Which bucket do I put the outstanding balance in?

    The report has 4 buckets: Current, 31 to 60 Days, 61 to 90 Days, and 91 + Days

    Note: Since it is now September, everything outstanding in August and everything upcoming in this month will be current, everything in July will be in 31 to 60, everything in June will be in 61 to 90, everything in May and before will be in 91+ ...

    Are we having fun yet????

    Thanks!
    Back to Access ... ADO is not the way to go for speed ...

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Ok ...

    Code:
                Select Case DateDiff("m", MyDate, ThisMonth)
                    Case 0, 1   ' Current Month and Previous Month
                        Bucket1 = Balance
                    Case 2      ' Month before that
                        Bucket2 = Balance
                    Case 3      ' Month before that
                        Bucket3 = Balance
                    Case Else   ' All prior months
                        Bucket4 = Balance
                End Select
    Does the trick ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    ... And for those of you who are looking for some real punishment, here's my full production code (within the A/R report itself) ...

    Code:
    Function CalcChgsAndBalance(ByVal WONumber As String, ByRef AccruedChgs As Currency, ByRef OpenBalance As Currency) As Integer
        On Error GoTo Err_CCAB
        
        Dim TmpChgs As Currency, TmpBalance As Currency, MarkUp As Double
        Dim TrgRecSet As ADODB.Recordset
        
        Set TrgRecSet = New ADODB.Recordset
        
        TrgRecSet.CursorType = adOpenForwardOnly
        TrgRecSet.LockType = adLockOptimistic
        TrgRecSet.CursorLocation = adUseClient
        
        TmpChgs = 0
        TmpBalance = 0
        MarkUp = 1.075
        
        ' Retrieve the Accrued Charges
        SQLString = "SELECT [WO Billing].*, [Work Orders II].RegHourMultiplier, [Work Orders II].OTHourMultiplier, [Work Orders II].DTHourMultiplier, [Work Orders II].[Material Markup]"
        SQLString = SQLString & " FROM [Work Orders II] INNER JOIN [WO Billing] ON [Work Orders II].WorkOrderNumber = [WO Billing].WorkOrderNumber"
        SQLString = SQLString & " WHERE ([WO Billing].WorkOrderNumber='" & WONumber & "');"
        TrgRecSet.Open SQLString, MyConnect
        If TrgRecSet.BOF = False Then
            TrgRecSet.MoveFirst
            TmpChgs = TrgRecSet.Fields(4).Value * TrgRecSet.Fields(9).Value + TrgRecSet.Fields(5).Value * TrgRecSet.Fields(10).Value + TrgRecSet.Fields(6).Value * TrgRecSet.Fields(11).Value + TrgRecSet.Fields(7).Value * TrgRecSet.Fields(12).Value + TrgRecSet.Fields(8).Value
            MarkUp = TrgRecSet.Fields(12).Value
        End If
        TrgRecSet.Close
       
        ' Retrieve the previous year Accrued Charges
        SQLString = "SELECT [WO YearEnd Summary].* FROM [WO YearEnd Summary] WHERE ([WO YearEnd Summary].WorkOrderNumber='" & WONumber & "');"
        TrgRecSet.Open SQLString, MyConnect
        If TrgRecSet.BOF = False Then
            TrgRecSet.MoveFirst
            ' Calculate the Accrued Charges
            TmpChgs = TmpChgs + TrgRecSet.Fields(4).Value + TrgRecSet.Fields(5).Value * TrgRecSet.Fields(6).Value
        End If
        TrgRecSet.Close
        
        ' Apply any Labor Adjustments
        SQLString = "SELECT * FROM [Labor Charge Adjustment] WHERE (WorkOrderNumber='" & WONumber & "');"
        TrgRecSet.Open SQLString, MyConnect
        If TrgRecSet.BOF = False Then
            TrgRecSet.MoveFirst
            While TrgRecSet.EOF = False
                TmpChgs = TmpChgs + TrgRecSet.Fields(2).Value
                TrgRecSet.MoveNext
            Wend
        End If
        TrgRecSet.Close
        TmpBalance = TmpChgs
            
        ' Deduct any previous Billings
        SQLString = "SELECT * FROM [WO Billing Detail] WHERE (WorkOrderNumber='" & WONumber & "');"
        TrgRecSet.Open SQLString, MyConnect
        If TrgRecSet.BOF = False Then
            TrgRecSet.MoveFirst
            While TrgRecSet.EOF = False
                TmpBalance = TmpBalance - TrgRecSet.Fields(5).Value
                TrgRecSet.MoveNext
            Wend
        End If
        TrgRecSet.Close
        
        ' Deduct any previous Cash Payments or Refunds
        SQLString = "SELECT * FROM Payments WHERE (WorkOrderNumber='" & WONumber & "');"
        TrgRecSet.Open SQLString, MyConnect
        If TrgRecSet.BOF = False Then
            TrgRecSet.MoveFirst
            While TrgRecSet.EOF = False
                If TrgRecSet.Fields(8).Value = True Then ' Refund
                    TmpChgs = TmpChgs - TrgRecSet.Fields(6).Value * MarkUp
                    TmpBalance = TmpBalance - TrgRecSet.Fields(6).Value * MarkUp
                Else
                    TmpBalance = TmpBalance - TrgRecSet.Fields(6).Value
                End If
                TrgRecSet.MoveNext
            Wend
        End If
        TrgRecSet.Close
        
        ' Deduct any previous year Cash Refunds
        SQLString = "SELECT * FROM [Payments History] WHERE (WorkOrderNumber='" & WONumber & "');"
        TrgRecSet.Open SQLString, MyConnect
        If TrgRecSet.BOF = False Then
            TrgRecSet.MoveFirst
            While TrgRecSet.EOF = False
                If TrgRecSet.Fields(8).Value = True Then ' Refund
                    TmpChgs = TmpChgs - TrgRecSet.Fields(6).Value * TrgRecSet.Fields(10).Value
                    TmpBalance = TmpBalance - TrgRecSet.Fields(6).Value * TrgRecSet.Fields(10).Value
                Else
                    TmpBalance = TmpBalance - TrgRecSet.Fields(6).Value
                End If
                TrgRecSet.MoveNext
            Wend
        End If
        TrgRecSet.Close
        
        Set TrgRecSet = Nothing
        
        AccruedChgs = TmpChgs
        OpenBalance = TmpBalance
        
        CalcChgsAndBalance = 1
    Exit_CCAB:
        Exit Function
        
    Err_CCAB:
        
        MsgBox Err.Number & ": " & Err.Description
        CalcChgsAndBalance = 0
        Resume Exit_CCAB
    End Function
    
    Function RetrieveWOAttributes() As Integer
        On Error GoTo Err_RWOA
        
        Dim TotalCost As Currency, RemainingBalance As Currency
        Dim ThisMonth As Date, TmpDate As Date
        
        Dim TrgRecSet As ADODB.Recordset
        
        Set TrgRecSet = New ADODB.Recordset
        
        TrgRecSet.CursorType = adOpenDynamic
        TrgRecSet.LockType = adLockOptimistic
        TrgRecSet.CursorLocation = adUseClient
        
        SQLString = "DELETE FROM " & RptTbl & ";"
        CurrentProject.Connection.Execute SQLString, , adCmdText
        
        ThisMonth = CDate(DatePart("m", Date) & "/1/" & DatePart("yyyy", Date))
        ' Retrieve the Open W.O.'s
        SQLString = "SELECT [Work Orders II].WorkOrderNumber, [Work Orders II].[Project Title], UserBillingSettings.LastName & ', ' & UserBillingSettings.FirstName AS Expr1, [Work Orders II].[Estimated Date of Bill]"
        SQLString = SQLString & " FROM [Work Orders II] INNER JOIN UserBillingSettings ON [Work Orders II].OwnerID = UserBillingSettings.UserID"
        SQLString = SQLString & " WHERE (([Work Orders II].[Estimated Date of Bill]<#" & DateAdd("m", 1, ThisMonth) & "#));" ' ([Work Orders II].Closed=False) AND PmtReceived=False
        MyRecSet.Open SQLString, MyConnect
        If MyRecSet.BOF = False Then
            MyRecSet.MoveFirst
            SQLString = "SELECT * FROM " & RptTbl & " WHERE (1=0);"
            TrgRecSet.Open SQLString, CurrentProject.Connection
            While MyRecSet.EOF = False
                Select Case MyRecSet.Fields(0).Value
                    Case "VOID", "EMP", "FYI", "STOCK", "A7"
                        GoTo SkipRecordAdd
                End Select
                If Left(MyRecSet.Fields(0).Value, 2) = "OH" Then GoTo SkipRecordAdd
                TotalCost = 0
                RemainingBalance = 0
                CalcChgsAndBalance MyRecSet.Fields(0).Value, TotalCost, RemainingBalance
                If RemainingBalance < 1 Then GoTo SkipRecordAdd
                TrgRecSet.AddNew
                ' W.O. #
                TrgRecSet.Fields(0).Value = MyRecSet.Fields(0).Value & ""
                ' Project Title
                TrgRecSet.Fields(1).Value = MyRecSet.Fields(1).Value & ""
                ' Est Dt of Bill
                TrgRecSet.Fields(2).Value = MyRecSet.Fields(3).Value
                ' Account Rep
                TrgRecSet.Fields(3).Value = MyRecSet.Fields(2).Value & ""
                ' Total Cost
                TrgRecSet.Fields(4).Value = TotalCost
                ' Remaining Balance
                TrgRecSet.Fields(5).Value = RemainingBalance
                ' Current
                TrgRecSet.Fields(6).Value = 0
                ' 31 to 60 Days
                TrgRecSet.Fields(7).Value = 0
                ' 61 to 90 Days
                TrgRecSet.Fields(8).Value = 0
                ' 91 + Days
                TrgRecSet.Fields(9).Value = 0
                Select Case DateDiff("m", MyRecSet.Fields(3).Value, ThisMonth)
                    Case 0, 1   ' Current Month and Previous Month
                        TrgRecSet.Fields(6).Value = RemainingBalance
                    Case 2      ' Month before that
                        TrgRecSet.Fields(7).Value = RemainingBalance
                    Case 3      ' Month before that
                        TrgRecSet.Fields(8).Value = RemainingBalance
                    Case Else   ' All prior months
                        TrgRecSet.Fields(9).Value = RemainingBalance
                End Select
                TrgRecSet.Update
    SkipRecordAdd:
                MyRecSet.MoveNext
            Wend
            TrgRecSet.Close
        End If
        MyRecSet.Close
        
        Set TrgRecSet = Nothing
        
        LoadingDelay
        
        RetrieveWOAttributes = 0
    Exit_RWOA:
        Exit Function
        
    Err_RWOA:
        
        MsgBox Err.Number & ": " & Err.Description
        RetrieveWOAttributes = 0
        Resume Exit_RWOA
    End Function
    Enjoy if you dare ...
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    I'm surprised that there was no takers ...
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    a taker confused by your attempt to get an answer in days from data that doesn't hold any "day" information.

    did you make a special effort to get to year/month or is that the most-detailed data available?

    if you stuck with standard access-format dates, the answer comes tumbling out in days from a simple

    Now() - anyAccessDateType

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by izyrider
    a taker confused by your attempt to get an answer in days from data that doesn't hold any "day" information.

    did you make a special effort to get to year/month or is that the most-detailed data available?

    if you stuck with standard access-format dates, the answer comes tumbling out in days from a simple

    Now() - anyAccessDateType

    izy
    Izy,

    Looks like some confusion between what I'm trying to do versus what I display for the end-user ... Since I have only month and year for a billing date my "bucketizing" (which age group to stick a particular bill in) is by definition fuzzy around the edges ... I was only looking for something close (which I did myself) where if the bill originated in a particular month that's the age bucket it fell in relative to the current month.

    By definition I cannot do a true Aging report ... I can only get it within a monthly ballpark - whisch is what my client is happy with.
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •