Access RollUp Date Problems
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
Re: Access RollUp Date Problems
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.
Re: Access RollUp Date Problems
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 ???
Re: Access RollUp Date Problems
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...
Re: Access RollUp Date Problems
Valid Point
Here's the code:
VB 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
Re: Access RollUp Date Problems
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?
Quote:
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?
Re: Access RollUp Date Problems
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.
Re: Access RollUp Date Problems
Quote:
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
Quote:
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.
Quote:
Do you have to use an Access 97 database.
At this point yes.
Quote:
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.
-------------------------------------
Re: Access RollUp Date Problems
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?
Re: Access RollUp Date Problems
Quote:
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.
Quote:
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
Re: Access RollUp Date Problems
dw85745,
There would hardly ever be any memory leaks if that were the case.
Re: Access RollUp Date Problems
How about trying using Action Queries for your database insertions? And perhaps use ado...
Re: Access RollUp Date Problems
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...
