Help! Timeline Report / Min Date (1 Viewer)

[Michele H]

Hello all,

I have a similar question that I found on a old post from another member... her question was never answered, I am hoping someone out there can help me figure this out. I am new to Access so forgive me - I am clueless!

Here is the question... (copied from old post from another member)...

Have created a report based on my table [tblprojectresource], from this table it takes [project] (project name), [registered] (start date of project) and [datereqd] (expected end date of project). The report then uses the following code to display project names and a bar showing the number of days (using datediff) of the project. The timeline takes the minstartdate and maxenddate - the problem i am having with this is that some projects can take a year!! So the timeline itself isn't that accurate for scheduling purposes. What i really need is to view this on a monthly/two monthly basis. Have included code as below:-

Option Compare Database
Option Explicit

Private mdatEarliest As Date
Private mdatLatest As Date
Private mintDayDiff As Integer

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim intStartDayDiff As Integer
Dim intDayDiff As Integer
Dim sngFactor As Single

On Error Resume Next

Me.ScaleMode = 1 'Twips
sngFactor = Me.boxMaxDays.Width / mintDayDiff

If Not IsNull(Me.Registered) And Not IsNull(Me.DateReqd) Then
Me.boxGrowForDate.Visible = True
Me.lblTotalDays.Visible = True
intStartDayDiff = Abs(DateDiff("d", Me.Registered, mdatEarliest))
intDayDiff = Abs(DateDiff("d", Me.DateReqd, Me.Registered))

If intStartDayDiff = 0 Then intStartDayDiff = 1
With Me.boxGrowForDate
.Left = Me.boxMaxDays.Left + (intStartDayDiff * sngFactor)
.Width = intDayDiff * sngFactor
End With
Me.lblTotalDays.Left = Me.boxGrowForDate.Left
Me.lblTotalDays.Caption = intDayDiff & " Day(s)"
Else '
Me.boxGrowForDate.Visible = False
Me.lblTotalDays.Visible = False
End If
End Sub

Private Sub Report_Open(Cancel As Integer)
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT Min([Registered]) AS MinOfStartDate " _
& " FROM TblProjectResource", dbOpenSnapshot)
If rs.RecordCount > 0 Then
mdatEarliest = rs!MinOfStartDate
End If
Set rs = db.OpenRecordset("SELECT Max(IIf(IsDate([DateReqd]),CDate([DateReqd]),Null)) " _
& "AS MaxOfEndDate FROM TblProjectResource", dbOpenSnapshot)
If rs.RecordCount > 0 Then
mdatLatest = rs!MaxOfEndDate
End If

mintDayDiff = DateDiff("d", mdatEarliest, mdatLatest)

Me.txtMinStartDate.Caption = Format(mdatEarliest, "dd/mm/yyyy")
Me.txtMaxEndDate.Caption = Format(mdatLatest, "dd/mm/yyyy")
Set rs = Nothing
Set db = Nothing
End Sub

(I found a sample timeline db and adapted it to suit my db)

Can anyone point me in the right direction??