Results 1 to 9 of 9
  1. #1
    GaryElwood is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    12

    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

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Can you provide project (or at least an extract) for analyis?

    Google: Gantt chart Microsoft Access

    Found a simple example that uses graph object and this site that offers a complex example not using graph object http://www.bandwood.com/gbs.htm
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    GaryElwood is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    12

    Gantt Chart code...

    This is some of the code used to create the bars....

    ......
    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
    If (DatePart("yyyy", [Current Start Date]) = 2010) Then
    WD = WD - ((13 - DatePart("m", [Current Start Date])) * 330)
    End If

    'Check for data errors
    If ([Current Start Date] > [Current End Date]) Then
    Me.ERR = "Data Error"
    Else
    Me.ERR = Null
    End If


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

    'If (Sloc + WD >= 14550) Then
    If (DatePart("yyyy", [Current End Date]) = 2014) Then
    Sloc14 = 14000 - Sloc
    End If

    'See if bar will fit on page (if bar width is greater than page width than make bar smaller)
    'If (Sloc + WD >= 14550) Then
    If (DatePart("yyyy", [Current End Date]) = 2014) Then
    Me.boxGrowForDate.Left = Sloc
    If (Sloc14 < 0) Then
    Me.boxGrowForDate.Width = 330
    Else
    Me.boxGrowForDate.Width = Sloc14
    End If
    Me.Post10.Visible = True
    Me.BoxLabel.Left = Sloc
    'Me.CSOL = Sloc14
    Else
    Me.boxGrowForDate.Left = Sloc
    Me.boxGrowForDate.Width = WD
    Me.Post10.Visible = False
    Me.BoxLabel.Left = Sloc
    Me.BoxLabel.Width = (CharW * 80)
    '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 "2010 Carryover"
    Me.boxGrowForDate.BackColor = lngBlu
    Case "2011 Growth Initiative"
    Me.boxGrowForDate.BackColor = lngVil
    Case "2011 On Deck"
    Me.boxGrowForDate.BackColor = lngOrn
    Case "2011 Small Enhancements"
    Me.boxGrowForDate.BackColor = lngWht
    Case "Cross-LOB"
    Me.boxGrowForDate.BackColor = lngYel
    Case "Merger Day 2"
    Me.boxGrowForDate.BackColor = lngLbu
    Case "Merger Non-Discretionary"
    Me.boxGrowForDate.BackColor = lngPnk
    Case "TOG Funded"
    Me.boxGrowForDate.BackColor = lngBrn
    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
    .......

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Just reading a ton of code is non-productive. It all looks good. Need to test. Would have to work with the project. Have you step debugged?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    brettbarton is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2011
    Posts
    3
    Hi Gary,

    I am looking to do something very similar. Is there any way you can share the report, the form and the VB code behind the form and report? Not sure if this code is comsidered proprietary. If so, I understand, but I thought I would ask before I try to recreate the wheel.

    Thanks,
    Brett

    Brett Barton
    brett.barton@hp.com

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Gary has ignored this thread for weeks. You might try a PM to him, maybe he has it set to send email notification.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Magic-Rat is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2012
    Posts
    1
    Same here. I would love to get a copy ...

  8. #8
    GoliathRulz68 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    7
    Just wondering if I could get a copy of the template for this report. I'm trying to do the same type of thing in MS Access 2010 and not really wanting to re-invent the wheel.

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The OP of this thread never provided the project. Review this link for other examples https://www.accessforums.net/showthr...duling-Presses
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 04-17-2012, 12:56 PM
  2. Replies: 2
    Last Post: 07-12-2010, 05:39 AM
  3. Chart report leading zeros
    By mulefeathers in forum Reports
    Replies: 9
    Last Post: 06-02-2010, 08:49 AM
  4. Replies: 5
    Last Post: 01-14-2010, 03:37 PM
  5. Pie Chart Design for Report
    By maggioant in forum Reports
    Replies: 0
    Last Post: 10-13-2009, 03:06 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums