Results 1 to 5 of 5
  1. #1
    Join Date
    May 2003
    Posts
    26

    Unanswered: Value of Previous Record

    I have a table using the fields:

    Month (text)
    Received (number)
    Assigned (number)
    Outstanding (number)

    Results:

    Jan 10 3 7
    Feb 14 7 7
    Mar 10 5 5

    I need to produce a query with an expression to calculate the sum for the Outstanding value of both the current and previous records. Results of query would be:

    Jan 10 3 7
    Feb 11 4 7 14
    Mar 10 5 5 19

    I've searched the MS Knowledge base and found an article which only allowed retrival of a previous record using the MAX function .... which will not work for what I am trying to accomplish.

    Any assistance in solving this would be greatly appreciated.

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    Does it matter to you if this is done through code?
    Last edited by CyberLynx; 01-16-04 at 02:43.

  3. #3
    Join Date
    May 2003
    Posts
    26
    Doesn't really matter how I get there ... it is the end result that counts!

    Any assistance is GREATLY appreciated.

    Regards,

  4. #4
    Join Date
    Nov 2003
    Posts
    1,487
    The function I have created here will sum up all records within your table where the Outstanding field contains a number greater than zero (or Null). How and where you use this function is up to you:

    Usage:

    myVariable = SumUpOutstanding

    OR

    Me.myControl = SumUpOutstanding

    OR Whatever ...

    The Function - Copy & Paste into the declarations section of a database code module:

    Code:
    Public Function SumUpOutstanding() As Long
        Dim dbs As Database, rst As Recordset
        Dim strCriteria As String
    
        SumUpOutstanding = 0	
        'Return reference to current database.
        Set dbs = CurrentDb
        'Define search criteria.
        strCriteria = "[Outstanding] > 0"
        ' Create a dynaset-type Recordset object based on your specific table.
        Set rst = dbs.OpenRecordset("myTableName", dbOpenDynaset)
        ' Find first matching record.
        rst.FindFirst strCriteria
        ' Check if record is found.
        If rst.NoMatch Then
            rst.Close
            Set dbs = Nothing
            GoTo NoRecordsFound
        Else
            ' Find other matching records.
            Do Until rst.NoMatch
                 SumUpOutstanding = SumUpOutstanding + rst!Outstanding
                 rst.FindNext strCriteria
            Loop
        End If
        rst.Close
        Set dbs = Nothing
        Exit Function
    
    NoRecordsFound:
            MsgBox "No Outstanding:@@" & "The are no Outstanding Accounts at this time, vbOKOnly + vbInformation, "No Outstanding "
    End Function
    Hope this helps in some way...
    Last edited by CyberLynx; 01-18-04 at 03:56.

  5. #5
    Join Date
    Feb 2004
    Location
    Williams Bay, Wisconsin
    Posts
    2
    Hi,

    This seems like something I can use but not good at code. I pasted the code in a new module. My record source is a query that feeds a report. Do I add the "outstanding" field to the query? and also, where do you enter the Usage:

    myVariable = SumUpOutstanding

    OR

    Me.myControl = SumUpOutstanding


    Sorry if I am not explaining too well. Any help will be appreciated.
    thanks.
    Len

Posting Permissions

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