Twips? Trying to set the scale for a timeline style report

[Plumdodge]

Access 2000 SP3
I am using the code from Dev Ashish from
http://www.mvps.org/access/reports/rpt0018.htm

I have modified (or tried to) in order to fit a fixed business week with the
start date monday and end date Friday of the current week. This will be used
to show the current workload for staff. Some project could start prior to the
week or end after the week but I'd like to see them represnted graphicly by
the timeline.

The two problems I am having and can not figure out are:

1) The size of the boxGrowForDate does not change. There must be something I
have altered to cause this, but for the life of me I can't figure out why?

2) How can I make it use the starting date of Monday of the current instead
of the actaul start date when it precedes monday of this week? I think I must
need IIF somewhere but I am unsure where.

Here is the code behind it, any ideas or suggestions are welcome and if
someone knows the definition of Twips I'd be quite interested too! Thanks,
The error trapping isn't done yet so it's real simple

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)
On Error GoTo Optout
Dim intStartDayDiff As Integer
Dim intDayDiff As Integer
Dim sngFactor As Single

On Error Resume Next

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

'Deal with dates which are populated'
If Not IsNull(Me.T_Start_Date) And Not IsNull(Me.T_Stop_Date) Then
Me.boxGrowForDate.Visible = True
Me.lblTotalDays.Visible = True


'intStartDayDiff = Distance from Start
'intDayDiff = Day length of entry

intStartDayDiff = Abs(DateDiff("d", Me.T_Start_Date, mdatEarliest))
intDayDiff = Abs(DateDiff("d", Me.T_Stop_Date, Me.T_Start_Date))

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

Me.int = sngFactor
Me.sng = intDayDiff

Optexit:
Exit Sub


Optout:
MsgBox "Can't display"
Resume Optexit

End Sub

Private Sub Report_Open(Cancel As Integer)
On Error GoTo Optout

'Set Start Date to Monday of this week
mdatEarliest = Date - Weekday(Date, vbMonday) + 1

'Set End Date to Friday of this week
mdatLatest = Date - Weekday(Date, vbFriday) + 8

'Get Scale
mintDayDiff = DateDiff("d", mdatEarliest, mdatLatest)

'Slap some values on the labels

Me.txtMinStartDate.Caption = Format(mdatEarliest, "mm/dd/yyyy")

Me.day2.Caption = Format(mdatEarliest + 1, "mm/dd/yyyy")

Me.Day3.Caption = Format(mdatEarliest + 2, "mm/dd/yyyy")

Me.day4.Caption = Format(mdatEarliest + 3, "mm/dd/yyyy")

Me.txtMaxEndDate.Caption = Format(mdatLatest, "mm/dd/yyyy")



Optexit:
Exit Sub


Optout:
MsgBox "Can't display"
Resume Optexit

End Sub

 

[ddunks]

I'm working n the same type of project using the same db as a reference. Do
not see a response for this entry - so I need some help. The number of days
calculated on the report is correct - but the bar is not sized on correctly
on 4 records (i.e 45 days is the same size as 5 days). Below is the code:

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.StartDate) And Not IsNull(Me.EndDate) Then
Me.boxGrowForDate.Visible = True
Me.lblTotalDays.Visible = True
intStartDayDiff = Abs(DateDiff("d", Me.StartDate, mdatEarliest))
intDayDiff = Abs(DateDiff("d", Me.EndDate, Me.StartDate))

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([Start Date]) AS MinOfStartDate " _
& " FROM qryProjectResourceTimeline", dbOpenSnapshot)
If rs.RecordCount > 0 Then
mdatEarliest = rs!MinOfStartDate
End If
Set rs = db.OpenRecordset("SELECT Max(IIf(IsDate([End Date]),CDate([End
Date]),Null)) " _
& "AS MaxOfEndDate FROM qryProjectResourceTimeline",
dbOpenSnapshot)
If rs.RecordCount > 0 Then
mdatLatest = rs!MaxOfEndDate
End If

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

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

 

[Plumdodge]

Hey Ddunks,
The code looks good man. If I may i'd suggest throwing in a msgbox and
puting the variables in the message. It sounds like either
intDayDiff or sngFactor
is comming out flat all the time.

I'm working n the same type of project using the same db as a reference. Do
not see a response for this entry - so I need some help. The number of days
calculated on the report is correct - but the bar is not sized on correctly
on 4 records (i.e 45 days is the same size as 5 days). Below is the code:

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.StartDate) And Not IsNull(Me.EndDate) Then
Me.boxGrowForDate.Visible = True
Me.lblTotalDays.Visible = True
intStartDayDiff = Abs(DateDiff("d", Me.StartDate, mdatEarliest))
intDayDiff = Abs(DateDiff("d", Me.EndDate, Me.StartDate))

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([Start Date]) AS MinOfStartDate " _
& " FROM qryProjectResourceTimeline", dbOpenSnapshot)
If rs.RecordCount > 0 Then
mdatEarliest = rs!MinOfStartDate
End If
Set rs = db.OpenRecordset("SELECT Max(IIf(IsDate([End Date]),CDate([End
Date]),Null)) " _
& "AS MaxOfEndDate FROM qryProjectResourceTimeline",
dbOpenSnapshot)
If rs.RecordCount > 0 Then
mdatLatest = rs!MaxOfEndDate
End If

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

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

Access 2000 SP3
I am using the code from Dev Ashish from
http://www.mvps.org/access/reports/rpt0018.htm

I have modified (or tried to) in order to fit a fixed business week with the
start date monday and end date Friday of the current week. This will be used
to show the current workload for staff. Some project could start prior to the
week or end after the week but I'd like to see them represnted graphicly by
the timeline.

The two problems I am having and can not figure out are:

1) The size of the boxGrowForDate does not change. There must be something I
have altered to cause this, but for the life of me I can't figure out why?

2) How can I make it use the starting date of Monday of the current instead
of the actaul start date when it precedes monday of this week? I think I must
need IIF somewhere but I am unsure where.

Here is the code behind it, any ideas or suggestions are welcome and if
someone knows the definition of Twips I'd be quite interested too! Thanks,
The error trapping isn't done yet so it's real simple

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)
On Error GoTo Optout
Dim intStartDayDiff As Integer
Dim intDayDiff As Integer
Dim sngFactor As Single

On Error Resume Next

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

'Deal with dates which are populated'
If Not IsNull(Me.T_Start_Date) And Not IsNull(Me.T_Stop_Date) Then
Me.boxGrowForDate.Visible = True
Me.lblTotalDays.Visible = True


'intStartDayDiff = Distance from Start
'intDayDiff = Day length of entry

intStartDayDiff = Abs(DateDiff("d", Me.T_Start_Date, mdatEarliest))
intDayDiff = Abs(DateDiff("d", Me.T_Stop_Date, Me.T_Start_Date))

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

Me.int = sngFactor
Me.sng = intDayDiff

Optexit:
Exit Sub


Optout:
MsgBox "Can't display"
Resume Optexit

End Sub

Private Sub Report_Open(Cancel As Integer)
On Error GoTo Optout

'Set Start Date to Monday of this week
mdatEarliest = Date - Weekday(Date, vbMonday) + 1

'Set End Date to Friday of this week
mdatLatest = Date - Weekday(Date, vbFriday) + 8

'Get Scale
mintDayDiff = DateDiff("d", mdatEarliest, mdatLatest)

'Slap some values on the labels

Me.txtMinStartDate.Caption = Format(mdatEarliest, "mm/dd/yyyy")

Me.day2.Caption = Format(mdatEarliest + 1, "mm/dd/yyyy")

Me.Day3.Caption = Format(mdatEarliest + 2, "mm/dd/yyyy")

Me.day4.Caption = Format(mdatEarliest + 3, "mm/dd/yyyy")

Me.txtMaxEndDate.Caption = Format(mdatLatest, "mm/dd/yyyy")



Optexit:
Exit Sub


Optout:
MsgBox "Can't display"
Resume Optexit

End Sub