Access RollUp Date Problems

[dw85745]

Valid Point

Here's the code:

Private Sub CompressDay2Month(strSourceTBLName As String, strDestTBLName As String)
'Compress Selected Symbol to Monthly Data Using Daily Data
'Note: No Time stamp on Monthly
 
   On Error GoTo Error_CompressDay2Month
 
   Dim rsSource As Recordset
   Dim rsDest As Recordset
   Dim iMonth As Integer, iPrevMonth As Integer
   Dim dtmDate As Date, dtmPrevDate As Date
   Dim sngOpen As Single, sngMnthOpen As Single
   Dim sngHigh As Single, sngMnthHigh As Single
   Dim sngLow As Single, sngMnthLow As Single
   Dim sngClose As Single, sngPrevClose As Single
   
   '*******
   'STARTUP
   '*******
   iPrevMonth = 0
   sngMnthHigh = -999999.99
   sngMnthLow = 999999.99
 
   Set rsSource = DaoDb.OpenRecordset(strSourceTBLName)
   Set rsDest = DaoDb.OpenRecordset(strDestTBLName)
   
   '****
   'MAIN
   '****
   With rsSource
 
      'Get All Daily Records
      Do Until .EOF
      
         'Get a Record
         dtmDate = ExtractDate(!fldHistDateTime)  'CRITICAL Requires DB Format of mm/dd/yyyy
         sngOpen = !fldHistOpen
         sngHigh = !fldHistHigh
         sngLow = !fldHistLow
         sngClose = !fldHistClose
         
         'Get Integer Month
         iMonth = Month(dtmDate)
 
         'For 1st Record Only
         If iPrevMonth = 0 Then
            iPrevMonth = iMonth
            sngMnthOpen = sngOpen
         End If
         
         'Compare Current Record to Prev Record
         If (iMonth > iPrevMonth) Or (iMonth < iPrevMonth) Then
         
            rsDest.AddNew
            
             'Full Month Processed so Put EOM date on
            rsDest!fldHistDateTime = CombineDateTime(EndOfMonth(dtmPrevDate), 0)
            rsDest!fldHistOpen = sngMnthOpen
            rsDest!fldHistHigh = sngMnthHigh
            rsDest!fldHistLow = sngMnthLow
            rsDest!fldHistClose = sngPrevClose
            
            rsDest.Update
            
            '1st rcd of month has been read, so save Open
            sngMnthOpen = sngOpen
            sngMnthHigh = -999999.99
            sngMnthLow = 999999.99
            
         End If
               
         If sngHigh > sngMnthHigh Then sngMnthHigh = sngHigh
         If sngLow < sngMnthLow Then sngMnthLow = sngLow
          
         iPrevMonth = iMonth
         dtmPrevDate = dtmDate
         sngPrevClose = sngClose
         
         .MoveNext
         
         'Get any messages if mouse or keyboard used
         If GetInputState() Then DoEvents
         
      Loop
   End With
      
   'Make Sure Store Last Partial Month in DaoDb
   'NOTE: Record may or may NOT be EOM
   With rsDest
      .AddNew
      
      'Note: No Time on Monthly
      !fldHistDateTime = CombineDateTime(dtmPrevDate, 0)  '<May or May Not be EOM
      !fldHistOpen = sngMnthOpen
      !fldHistHigh = sngMnthHigh
      !fldHistLow = sngMnthLow
      !fldHistClose = sngPrevClose
      .Update
   End With
   
   rsSource.Close
   rsDest.Close
   
   '******
   'WrapUP
   '******
   
   Exit Sub
 
End Sub