Access report dates do not show intermittantly

[rjmccorkle]

I am using VB6 to control an Access97 database. I wrote a report in Access that is based on a query. The report is in a temporary database since part of the process is filling a table with current info, printing the report, then deleting all the rows and compacting the database. I don't want to take a chance messing up the original database with all the file manipulations.
Part of the query is based on a table in the original database (the names & addresses) and part in the imported records in the temporary database. The imported records include 2 date fields, a starting and an ending date. All records have a starting date, not all have an ending date. The purpose of the report is to calculate the number of days each record has in a user supplied date range. If there is no ending date in the record, it is assumed that the date range ending date is used.
First I import the records that meet the date criteria to the temporary database then I do an update on the temporary table: it contains 2 imported date fields,Starting and Ending, and 2 empty date fields, FirstDay and LastDay. The update routine fills in FirstDay with Starting if Starting is >= dateRangeStart, otherwise it uses the dateRangeStart, and in similar fashion fills the LastDay based on the Ending date in the record and the dateRangeEnding date.
In the report I show the Start date, Ending date,FirstDay and LastDay and days calculated by subtracting Firstday from LastDay.
When the user is done viewing the report, the records in the temporary database get deleted and that database gets compacted.
Here is the problem: I run it once and it prints fine. I run it again and all the records appear; but the records with Ending dates do not display the FirstDay and LastDay fields and the number of days field is blank(its formula is (Lastday-Firstday)+1. If I do any changes to the report, then run the VB program again, it prints OK the first time but not subsequent times.
ANY help would be GREATLY appreciated.
Bob
104122.315@compuserve.com [sig][/sig]

 

[swilliams]

I think to solve this, I would need to see the report and the relevant code. Is there any chance of this??

Simon [sig][/sig]

 

[rjmccorkle]

Simon, Here is the code calling the report from VB. The date fields that are stored with each client are DOP and DOM. Then toward the end right before the report is called in Access, FirstDay and Lastday are the calculated fields showing which dates from this client fall within the given date range, vStart and vEnd. It's FirstDay and LastDay that show up whimsically on the report. I found that if I run the report procedure and the report does not show all the data then open a second instance of Access and preview the report it looks fine. I REALLY appreciate any help. I've spent hours trying things. Usually it's a learning process but this time I haven't found any gems along the way. How do I get a copy of the temporary database with the report definition to you? Email attachment? It is 102Kb. I just commented out the last dozen lines of the below code (the delete &compact routine) to save the raw data for your observation and the report ran fine 10 times. I marked the section with a NOTE! A possible hint? Thanks for the help.
Here is the VB code:

Private Sub cmdReimburse_Click()
Dim dbfBHTemp As Database, vStart As String, vEnd As String, vMonth As Integer
'use vF to store report style choice 0=all no pagebrk 1=all w/pgbrk 2=one
Dim vF As Integer, vPLCID As Integer, vRep As String
Dim objAccess As New Access.Application
frmReimbRep.Left = cmdReimburse.Left + frmChild.Left - frmReimbRep.Width / 2
frmReimbRep.Top = cmdReimburse.Top + frmChild.Top - frmReimbRep.Height + 100
frmReimbRep.Caption = "Reimbursement Report"
'set start day to first day of previous month
frmReimbRep.txtRange(0) = IIf(Month(Now) = 1, "12/1/" & Year(Now) - 1, Month(Now) - 1 & "/1/" & Year(Now))
'call fnclastday to get last day of previous month
frmReimbRep.txtRange(1) = fncLastDay(CDate(frmReimbRep.txtRange(0).Text))
frmReimbRep.Show 1
If frmReimbRep.txtRange(0).Text = "" Then Unload frmReimbRep: Set objAccess = Nothing: Exit Sub
On Error GoTo UpdateErr
vStart = "#" & frmReimbRep.txtRange(0).Text & "#"
vEnd = "#" & frmReimbRep.txtRange(1).Text & "#"
For vF = 0 To 2
If frmReimbRep.optReport(vF).Value Then Exit For
Next vF
If vF = 2 Then vPLCID = frmReimbRep.cboPlcNames.Tag
Unload frmReimbRep
Set dbfBHTemp = DBEngine.Workspaces(0).OpenDatabase(BHTemp_Path)
'delete any old rows from temp database
strSQL = &quot;DELETE * FROM [PlcHistTemp] WHERE [CID]<>Null&quot;
dbfBHTemp.Execute (strSQL), dbFailOnError
'load BHTemp database with Current plcmts with dates<=EndDate
Set dbfBHTemp = DBEngine.Workspaces(0).OpenDatabase(BHTemp_Path)
strSQL = &quot;INSERT INTO [PlcHistTemp] ([CID],[Child],[SSN],[PLCID],[DOP],[Rate],[LastDay],[StartDate],[EndDate]) SELECT &quot;
strSQL = strSQL & &quot;[CID],&quot;
strSQL = strSQL & &quot;([LastName] & ', ' & [FirstName] & ' ' & [MI]),&quot;
strSQL = strSQL & &quot;[SSN],[PLCID],[DOP],[Rate],&quot; & vEnd & &quot;,&quot; & vStart & &quot;,&quot; & vEnd
strSQL = strSQL & &quot; FROM [Child] IN '&quot; & BH_Path
If vPLCID > 0 Then
strSQL = strSQL & &quot;' WHERE [PLCID]=&quot; & CStr(vPLCID) & &quot; AND [DOP]<=&quot; & vEnd & &quot;;&quot;
Else
strSQL = strSQL & &quot;' WHERE [DOP]<=&quot; & vEnd & &quot;;&quot;
End If
dbfBHTemp.Execute (strSQL), dbFailOnError
'load BHTemp database with PlcHist that meet date requirements
strSQL = &quot;INSERT INTO [PlcHistTemp] ([CID],[Child],[SSN],[PLCID],[DOP],[DOM],[Rate],[StartDate],[EndDate]) SELECT &quot;
strSQL = strSQL & &quot;[CID],[ChName],&quot;
strSQL = strSQL & &quot;[PlcHist].[SSN],[PLCID],[PlcHist].[DOP],[DOM],[PlcHist].[Rate],&quot; & vStart & &quot;,&quot; & vEnd
strSQL = strSQL & &quot; FROM [qPlcHist] IN '&quot; & BH_Path
If vPLCID > 0 Then
strSQL = strSQL & &quot;' WHERE [PLCID]=&quot; & CStr(vPLCID) & &quot; AND [PlcHist].[DOP]<=&quot; & vEnd & &quot; AND [DOM]>=&quot; & vStart & &quot;;&quot;
Else
strSQL = strSQL & &quot;' WHERE [PlcHist].[DOP]<=&quot; & vEnd & &quot; AND [DOM]>=&quot; & vStart & &quot; ORDER BY [PlcHist].[PLCID],[PlcHist].[DOP];&quot;
End If
dbfBHTemp.Execute (strSQL), dbFailOnError
'update BHTemp database to show first and last dates of each child in
'chosen range; use vStart if Dop is less then vStart, otherwise use DOP
'for firstday of current placements where DOM is null
strSQL = &quot;UPDATE [PlcHistTemp] SET [FirstDay]=(Iif([DOP]<=&quot; & vStart & &quot;,&quot; & vStart & &quot;,[DOP]))&quot;
strSQL = strSQL & &quot; WHERE [DOM]=Null&quot;
dbfBHTemp.Execute (strSQL), dbFailOnError
'update plchist records
strSQL = &quot;UPDATE [PlcHistTemp] SET [FirstDay]=(Iif([DOP]<=&quot; & vStart & &quot;,&quot; & vStart & &quot;,[DOP])),&quot;
strSQL = strSQL & &quot;[LastDay]=(Iif([DOM]>=&quot; & vEnd & &quot;,&quot; & vEnd & &quot;,[DOM]))&quot;
strSQL = strSQL & &quot; WHERE [DOM]<>Null&quot;
dbfBHTemp.Execute (strSQL), dbFailOnError
'preview report
vRep = IIf(vF = 0, &quot;Reimburse&quot;, &quot;Reimburse2&quot
With objAccess
.OpenCurrentDatabase filepath:=BHTemp_Path
.DoCmd.OpenReport ReportName:=vRep, View:=2, wherecondition:=&quot;[PlcNames].[PlcType] LIKE '*Fost*'&quot;
'.DoCmd.OpenReport ReportName:=&quot;Test&quot;, View:=2, wherecondition:=&quot;[PlcNames].[PlcType] LIKE '*Fost*'&quot;
.DoCmd.Maximize
.DoCmd.ShowToolbar &quot;RepPrev&quot;, acToolbarNo
.Visible = True
End With
'send Alt(%) space x to maximize Access app then right and down
'to position report for viewing
SendKeys (&quot;% x{right}{down}&quot
'Give up CPU control
DoEvents
MsgBox &quot;Report complete&quot;, vbOKOnly
objAccess.Quit
Set objAccess = Nothing
'delete rows from temp database then compact it
'compact generates a new file so delete the old then rename the new
NOTE! I COMMENTED OUT THE NEXT BATCH OF LINES UPTO &quot;EXIT SUB&quot;
AND THE REPORT SEEMED TO WORK OK
strSQL = &quot;DELETE * FROM [PlcHistTemp] WHERE [CID]<>Null&quot;
dbfBHTemp.Execute (strSQL), dbFailOnError
dbfBHTemp.Close
DBEngine.CompactDatabase BHTemp_Path, Left(BHTemp_Path, Len(BHTemp_Path) - 4) & &quot;1.mdb&quot;
Kill BHTemp_Path
Name Left(BHTemp_Path, Len(BHTemp_Path) - 4) & &quot;1.mdb&quot; As BHTemp_Path
Exit Sub
UpdateErr:
MsgBox Err.Description & Chr(13) & &quot;from &quot; & Err.Source & &quot; -- Number:&quot; & CStr(Err.Number)
Exit Sub
End Sub

Thanks again.
Bob
104122.315@compuserve.com [sig][/sig]

 

[swilliams]

Hi Bob,
As a general rule, if I am going to use a temporary table to base a report on, I always clear the table first, then insert records into the table. This way, if the process failed last time, any redundant records are deleted from the table before I add records.

If you do this at the top of your routine, you can then do the compacting,.. before you run the report. If commenting out these lines made the report work, then maybe putting them first will also allow the report to work??

Simon [sig][/sig]

 

[rjmccorkle]

Hi Simon,
This just gets stranger. I was already deleting the temporary rows before and after the report. When I went back to experiment some more with the compacting remarked out, it acted weird again. So I copied it on to the Pentium 133 with Win95 at the shop, where the database is in actual use, and the report ran 4 times OK, but running it on my PIII notebook with Win98 goofed up after the 2nd time; that was without the compacting. I tried changing the routine so that it copied the original temp database without data to another temp database then use the copy. The copying worked in Win98 but not Win95 (is there ANYTHING that is straightforward in VB?) and it didn't make a difference printing the report on my machine, so the compacting isn't causing the problem. I have sent the report to print preview using VB, where the dates do not show up, then opened the db in Access and previewed the same report and all the dates show. I may just have to warn the user about the situation and teach them how to run the report from Access.
This is what I got from VB to copy the empty database; any ideas why it bombs in Win95? Or do you know of a different method for copying a file to a different name?
'Dim fileSystem
'Set fileSystem = CreateObject(&quot;Scripting.FileSystemObject&quot
'fileSystem.CopyFile BHTemp_Path, BHT_Path, True

Thanks for the time and help.
Bob [sig][/sig]

 

[swilliams]

To copy files, I normally just use
FileCopy source, destination

This is weird sh1t and no, nothing is ever easy in MS, not just VB.

Simon [sig][/sig]

 

[rjmccorkle]

Simon,
Your help with the FileCopy made the 15 hours of time I've spent spinning my wheels all worthwhile! It sure would have been nice if when I did a search in MSDN for how to copy files it would have popped up instead of that CopyFile stuff I found.
The problem appears to have gone away as mysteriously as it appeared. I modified the 2 reports (one has page breaks after a grouping, the other doesn't, other wise they are the same) by adding a couple of fields and now when I run the reports the dates showed up 30 times in a row. One of the text boxes mysteriously appeared and disappeared but I fixed that (I think) by closing the temp database after I populated it and then reopened it in the print preview section of code. Before I was closing it after printing the report.
Thanks again for your help. It's hard for me to understand why businesses have ever put up with the huge disparity in time and resources it takes to do things in Windows versus DOS. I'm still a neophyte at VB, but I could have written the whole project I've done to date (2 months) in 2 weeks in RBase and it would have been twice as fast and take up a quarter of the space.
Thanks for the nugget of info.
Bob [sig][/sig]