Hot Fox: Re: [Access VBA Central] Rolling 30 Days Add in -1 Point

domingo, 24 de maio de 2009

Re: [Access VBA Central] Rolling 30 Days Add in -1 Point

This almost sounds like a recursive funciton of a sorts.

Find and Set First-Date... Set Positive_Count
While Not Finished
     Find Next-Date
     If Next-Date is 30 days more then First-Date
          If Positive_Count - 1 > 0 then
               insert record 30 days from First-Date
               Positive_Count = Positive_Count - 1
     Else
          set First-Date = Next-Date
          Positive_Count = Positive_Count + Next_Count

Note this is also assuming that a -1 is put in for each full 30 days from the last record positive record and that if the Next-Date appears 91 days later that the -1 records would be inserted dropping the initial 5 points down to 2 points --- however if 181 days occurs between our 5 point First-Date and your Next-Date you don't Insert that extra record because you already have five -1 records making the net balance 0 --- that is unless you can earn -1 days.

This could be built as a T-SQL or PL/SQL statement but since your using VBA I'd suggest building it as a mini-program triggered by a button. Also I'd store somewhere the last processed date or... add a field called processed so you don't always start at the very beginnning.

I hope that helps some.

[Non-text portions of this message have been removed]