Results 1 to 1 of 1
  1. #1
    Join Date
    Sep 2011
    Posts
    5

    Question 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
    Select Case Me.Roadmap_Segment__aka__Funding_Source_
    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
    Attached Thumbnails Attached Thumbnails Roadmap.JPG  
    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
  •