My first post! And yes, newbie warning, newbie warning.....

I have been working on a form that displays running sums for sales totals and sales volumes for each of my company's salespersons. Each line will display the month and year, the salesperson's ID, the sales total for that month and the running sum of that total, month to month. The lines are grouped by salesperson and then by date; for example, if I choose 1/1/2003 to 5/31/2004 as my analysis period, the form will show month by month data, Jan 2001 to May 2004, for salesperson A, followed by the same for salesperson B, etc.

As first described, there were a couple of catches; the running sum would have to reset to just the current month's sales total:

a) if the year changed - i.e. a December running sum would show all sales that year, but the following row - January - would show just January's sales before starting to sum again;
b) if the salesperson ID changed.

I quickly figured out that DSum() was just as lumbering as everyone says, so I took the code from this Microsoft web page - web page - and made a few changes, and to my shock and horror, it worked!

Here's the code that worked: 'DateBox' is a 6-digit numeric date, yyyymm, and 'SalesName' is just the salesperson's initials.

CODE
   PrevDate = Val(Mid(RS(Datebox), 1, 4))
   PrevSales = RS(SalesName)
   Do Until RS.BOF
            NewDate = Val(Mid(RS(Datebox), 1, 4))
            NewSales = RS(SalesName)
            If PrevDate = NewDate And PrevSales = NewSales Then
            Result = Result + RS(FieldToSum)
            Else
            Result = Previous.RS(FieldToSum)
            End If
      RS.MovePrevious
   Loop


Great - from newbie to Access God, overnight! Or so I thought......

I was then asked to modify the form so the year for analysis could be any year-long period - not just January to December, but also March to February, September to August, or anything else. I have spent hours trying to get this to work, and no luck - probably has something to do with the fact that I don't know the first thing about VBA...

Anyway, here's the loop and surrounding code from one of my failed attempts. FutureDate is, as you see, just the current date + 100, which in the yyyymm format should give us the year and month a year on from now.

CODE
   FutureDate = Val(RS(Datebox))
   FutureDate = FutureDate + 100
   FutureDate = Str(FutureDate)
      
   PrevSales = RS(SalesName)
  
   Do Until RS.BOF
            CurrentDate = Val(RS(Datebox))
            CurrentDate = Str(CurrentDate)
            NewSales = RS(SalesName)
            If PrevSales = NewSales And FutureDate <> CurrentDate Then
                Result = Result + RS(FieldToSum)
            Else
                If PrevSales = NewSales And FutureDate = CurrentDate Then
                    Result = Previous.RS(FieldToSum)
                    FutureDate = Val(RS(Datebox))
                    FutureDate = FutureDate + 100
                    FutureDate = Str(FutureDate)
                Else
                    If PrevSales <> NewSales And FutureDate = CurrentDate Then
                        Result = Previous.RS(FieldToSum)
                        PrevSales = RS(SalesName)
                    Else
                        If PrevSales <> NewSales And FutureDate <> CurrentDate Then
                            Result = Previous.RS(FieldToSum)
                            PrevSales = RS(SalesName)
                            FutureDate = Val(RS(Datebox))
                            FutureDate = FutureDate + 100
                            FutureDate = Str(FutureDate)
                        End If
                    End If
                End If
            End If
        RS.MovePrevious
   Loop


The string conversions are there because I had luck with strings in the first code snippet, and are probably irrelevant.

I'm afraid the bottom line is I'm totally out of my depth; any suggestions or assistance would be greatly appreciated.

Many thanks.

James