Access RollUp Date Problems

[dw85745]

I have what I consider a unique problem with Access 97 dates.

PROBLEM:

Rollup daily data to monthly data.

Initially worked fine. About a year ago experienced problems.
Eliminated daily dates Prior to 1935 from daily table.
RollUp worked fine until about a week ago.
Repaired and Compressed DB and problem went away after several attempts.
Today blew up again.

Prior to Rollup:
1) Repair and Compress do NOT resolve the problem

During Rollup (stepping through VB code with breakpoints):
When Access gets to the February 1937 daily record it resets the pointer back to the first Daily record.


After Rollup:
1) Access ONLY rolled up Daily to Monthly records for 1/31/36 to 1/31/37.
2) Access deleted from the monthly table key field (fldDate) --> both the Required = Yes and the Format of mm/dd/yyyy


DATA RANGE:

01/02/1936 to current date

TABLES -- Daily and Monthly:

fldDate (Key Field -> Date, Required, Format mm/dd/yyyy)
fldHigh Number -> Single
fldLow Number -> Single
fldClose Number -> Single

------------------------------------------------------------------------------------

Thanks
David

[randem]

How big is your database? Access 97 always had problems if you did not code it correctly. Post some of your code that does the work.

[dw85745]

Thanks for response Randem.

1) DB not large

2) Nothing wrong with VB code as used same code for several years
and worked OK.

It is either an Access97 Issue

OR

an OS issue in the way MS defines their date and which Access uses.

But how to resolve ???

[randem]

NO, just because something works doesn't mean that it is ok. It just means that it survived till now. Access hasn't changed either. Without seeing code there is no solution just guesses. One must rule out what the problem isn't before one can find the true problem. Denial is not ruling it out...

Ex. A car runs with a pair of pantyhose for a fan belt without a problem for a long time. Sooner or later it will cease to work and not because it wasn't put on correctly at the beginning. It's because it should have never been there at all...

[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

[randem]

dw85745,

Code looks clean. One thing you don't do is to set you recordset to equal nothing when you are done. This is VB6 code correct?

When Access gets to the February 1937 daily record it resets the pointer back to the first Daily record.

Feb 1937 Daily record? I don't see any year calculations in the sub. How do you know not to roll-over?

[randem]

dw85745,

Access does have a problem if you don't maintain it on a regular basic. What you could do to test the database is to create an empty database then import the tables into the new one. Try your code on the new one to see if the problem still exist.

If your code is in VB6... Do you have to use an Access 97 database.

[dw85745]

One thing you don't do is to set you recordset to equal nothing when you are done. This is VB6 code correct?

We had many a debate on this one. When the local procedure exits the object is automatically set = Nothing by VB

What you could do to test the database is to create an empty database then import the tables into the new one. Try your code on the new one to see if the problem still exist.

Good idea -- will try -- but Repair and Compact should solve the problem.

Do you have to use an Access 97 database.

At this point yes.

How do you know not to roll-over?

Don't get your comment?
Months are numbered from 1 to 12 so this takes care of rollup since
in loop and Key field of Date/Time in Access should maintain in date order.

-------------------------------------

[randem]

dw85745,

Actually no, The object does not get set to nothing unless the sub created the object in the definitions. Otherwise the sub does not know it even exist since it never instanciated it. So it won't be done automatically. Otherwise be safe...

Anyway to get your project to test it?

[dw85745]

Actually no, The object does not get set to nothing unless the sub created the object in the definitions. Otherwise the sub does not know it even exist since it never instanciated it. So it won't be done automatically. Otherwise be safe...

Don't recall for sure, but I believe I posted in the codebank some time back
(year or two) either the link to this issue or the article itself.

Anyway to get your project to test it?

No on this one.

Off to bed since been up since 2:00am. Will spend some time tomorrow or this weekend and repost this thread.

Thanks for your time.

David

[randem]

dw85745,

There would hardly ever be any memory leaks if that were the case.

[dee-u]

How about trying using Action Queries for your database insertions? And perhaps use ado...

[pnish]

Are the years in your dates being written to the database as YYYY, ie the full 4 digit year num? I recall MS changed the way Access interpreted dates to try and combat the so called Y2K problem. I can't remember the cutoffs, but it was something like, eg if 31/1/29 was entered it would be assumed to be 31/1/2029, however if 31/1/30 was entered it would be treated as 31/1/1930.

Also the fact that this happened when it reached a February date may provide a clue, given that February has 29 days in a leap year. Perhaps there's some screwed up data in the database.

Just a thought...