# Thread: MS Access GANTT Chart report

1. Registered User
Join Date
Sep 2011
Posts
5

## Unanswered: MS Access GANTT Chart report

MS Access GNATT Chart Problems….HELP!
This is a bit confusing but thought maybe someone might have an idea.
I am creating a Gantt chart using a MS Access report. The report displays a list of projects our company is working on and it spans from January 20011 through January 2014. (Total of approximately 650 projects) and is broken down by months.
Most of the “Bars” on the chart display correctly…. The problem comes when a “Bar” width exceeds the width of the report. (i.e. project end date beyond December 2014) I check to see if the end of the Bar is greater than 14550 twips (that is approx 1 inch from the right edge of the report)
Calculating the bar location:
January 2011 is 1980 twips from the left side of the report. (This would be the starting point for and bar) so if the project start date is in the january of 2011, the SLOC (Starting Location) would = 1980.
Then I calculate how many months between the start and completion and multiple that times 330. (330 is the number of twips between each month) so if we have 4 months between start and end I multiply 330*4 = 1320 twips wide.
With me so far….?
Given the above we would have a bar with Left=1980 and Width=1320.
First bar on the chart! That was easy.

NOW FOR THE PROBLEM…
Now let’s say our start date is December 2013 (35 months from our base line of January 2011) so that would make our SLOC 35 * 330=11550+(our starting point 1980 twips) (Bar.Left = 13530). And our project is 6 months long (6*330=1980)so…. SLOC and WD = 15510 twips to the end of this project which exceeds the width of the report.

(boxGrowForDate is the name of the bar)

If (Sloc + WD >= 14550) Then
Me.boxGrowForDate.Left = Sloc
Me.boxGrowForDate.Width = (WD - ((Sloc + WD) - 14000))
Me.Post10.Visible = True
Me.BoxLabel.Left = Sloc
Else
Me.boxGrowForDate.Left = Sloc
Me.boxGrowForDate.Width = WD
Me.Post10.Visible = False
Me.BoxLabel.Left = Sloc
End If

For some reason after trimming the size of the bar (making the width smaller) sometime the bar staring location gets pushed way over to the left hand side of the report. Its like the bar is trying to exceed the width of the report…. But it shouldn’t be. And it only happens on some of the projects that extend to far into 2014.
I know this is a little confusing but any help would be appreciated!
Thanks
Gary

------------------------Report Detail code-------------------

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Dim db As Database
Dim rs As Recordset
Dim lngBlk As Long, lngLbu As Long, lngGbu As Long, lngGrn As Long, lngVil As Long, lngPnk As Long, lngBrn As Long, lngBlu As Long, lngYel As Long, lngOrn As Long, lngWht As Long
Dim Sloc As Integer
Dim Sloc14 As Integer
Dim SQ As Integer
Dim WD As Integer
Dim WDx As Integer
Dim CharW As Integer

On Error Resume Next

If Not IsNull(Me.[Program/Initiative/Product Alignment]) Then
Line161.Visible = True
End If
If IsNull(Me.[Program/Initiative/Product Alignment]) Or ((Me.[Program/Initiative/Product Alignment]) = "") Then
Line161.Visible = False
End If

'If (Me.[Project Status] = "Active") Then
'Me.FlagA.Visible = True
'Else
'Me.FlagA.Visible = False
'End If
'If (Me.[Project Status] = "Inactive") Then
'Me.FlagI.Visible = True
'Else
'Me.FlagI.Visible = False
'End If
With Me.boxGrowForDate
If Not IsNull(Me.[Current Start Date]) And Not IsNull(Me.[Current End Date]) Then
Me.boxGrowForDate.Visible = True
'Calculate bar starting position.........
If (DatePart("m", [Current Start Date]) = 1) Then
SQ = 0
End If
If (DatePart("m", [Current Start Date]) = 2) Then
SQ = 330
End If
If (DatePart("m", [Current Start Date]) = 3) Then
SQ = 660
End If
If (DatePart("m", [Current Start Date]) = 4) Then
SQ = 990
End If
If (DatePart("m", [Current Start Date]) = 5) Then
SQ = 1320
End If
If (DatePart("m", [Current Start Date]) = 6) Then
SQ = 1650
End If
If (DatePart("m", [Current Start Date]) = 7) Then
SQ = 1980
End If
If (DatePart("m", [Current Start Date]) = 8) Then
SQ = 2310
End If
If (DatePart("m", [Current Start Date]) = 9) Then
SQ = 2640
End If
If (DatePart("m", [Current Start Date]) = 10) Then
SQ = 2970
End If
If (DatePart("m", [Current Start Date]) = 11) Then
SQ = 3300
End If
If (DatePart("m", [Current Start Date]) = 12) Then
SQ = 3630
End If

If (DatePart("yyyy", [Current Start Date]) = 2010) Then
Sloc = 1995
End If
If (DatePart("yyyy", [Current Start Date]) = 2011) Then
Sloc = 1995 + SQ
End If
If (DatePart("yyyy", [Current Start Date]) = 2012) Then
Sloc = 5955 + SQ
End If
If (DatePart("yyyy", [Current Start Date]) = 2013) Then
Sloc = 9915 + SQ
End If
If (DatePart("yyyy", [Current Start Date]) = 2014) Then
Sloc = 13875 + SQ
End If

'Calculate bar width .........
If (DatePart("yyyy", [Current End Date]) - DatePart("yyyy", [Current Start Date]) = 0) Then
WD = (DatePart("m", [Current End Date]) - DatePart("m", [Current Start Date])) * 330
End If
If (DatePart("yyyy", [Current End Date]) - DatePart("yyyy", [Current Start Date]) = 1) Then
WD = (((DatePart("m", [Current End Date])) + (12 - DatePart("m", [Current Start Date]))) * 330)
End If
If (DatePart("yyyy", [Current End Date]) - DatePart("yyyy", [Current Start Date]) = 2) Then
WD = ((((DatePart("m", [Current End Date])) + (12 - DatePart("m", [Current Start Date]))) * 330) + 3960)
End If
If (DatePart("yyyy", [Current End Date]) - DatePart("yyyy", [Current Start Date]) = 3) Then
WD = ((((DatePart("m", [Current End Date])) + (12 - DatePart("m", [Current Start Date]))) * 330) + 7920)
End If

'Calculate title length
CharW = Len(Me.[Project Title] & [Planview ID])

If (Sloc + WD >= 14550) Then
Sloc14 = 13500 - Sloc
End If

'See if bar will fit on page
If (Sloc + WD >= 14550) Then
Me.boxGrowForDate.Left = Sloc
Me.boxGrowForDate.Width = Sloc14
Me.Post10.Visible = True
Me.BoxLabel.Left = 2000
Me.CSOL = Sloc14
Else
Me.boxGrowForDate.Left = Sloc
Me.boxGrowForDate.Width = WD
Me.Post10.Visible = False
Me.BoxLabel.Left = Sloc
Me.CSOL = 0
End If

If (DatePart("yyyy", [Current Start Date]) = 2010) Then
Me.Pre10.Visible = True
Else
Me.Pre10.Visible = False
End If

'Select color for bar
If (Me.[Funding Status] = "Approved/Funded") Then
Me.boxGrowForDate.BackColor = lngBlk
Else
Case "2011 LOB Investment"
Me.boxGrowForDate.BackColor = lngGrn
Case "Future Demand"
Me.boxGrowForDate.BackColor = lngGbu
Case "WFI/WFA Integration"
Me.boxGrowForDate.BackColor = lngBlk
....................
End Select
End If

If Not IsNull(Me.[Planview ID]) Then
Me.BoxLabel.Caption = Me.[Planview ID] & ": " & Me.[Project Title]
Else
Me.BoxLabel.Caption = Me.[Project Title]
End If
Else
Me.boxGrowForDate.Visible = False
End If

'------------ Calculate Funding Status: Partially Funded ---------------------

If Not IsNull(Me.[Current Start Date]) And (Not IsNull(Me.[Current End Date]) And (Me.[Funding Status] = "Partially Funded")) Then
Me.boxGrowForDateEXT.Visible = True
With Me.boxGrowForDateEXT
'Calculate bar starting position.........

If (DatePart("yyyy", [Planned End of Project]) - DatePart("yyyy", [Current End Date]) = 0) Then
WDx = (DatePart("m", [Planned End of Project]) - DatePart("m", [Current End Date])) * 330
End If
If (DatePart("yyyy", [Planned End of Project]) - DatePart("yyyy", [Current End Date]) = 1) Then
WDx = (((DatePart("m", [Planned End of Project])) + (12 - DatePart("m", [Current End Date]))) * 330)
End If
If (DatePart("yyyy", [Planned End of Project]) - DatePart("yyyy", [Current End Date]) = 2) Then
WDx = ((((DatePart("m", [Planned End of Project])) + (12 - DatePart("m", [Current End Date]))) * 330) + 3960)
End If
If (DatePart("yyyy", [Planned End of Project]) - DatePart("yyyy", [Current End Date]) = 3) Then
WDx = ((((DatePart("m", [Planned End of Project])) + (12 - DatePart("m", [Current End Date]))) * 330) + 7920)
End If

Me.boxGrowForDateEXT.Left = Sloc + WD
Me.boxGrowForDateEXT.Width = WDx
End With
Else
Me.boxGrowForDateEXT.Visible = False
End If
End With

End Sub
Last edited by GaryElwood; 09-16-11 at 11:17. Reason: Show report Detail code

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•