Timeline by Month 1

[Vittles]

I need to set up a pivot chart or table that looks like the basic example below:
Oct Nov Dec.........Sept
Project1 SD----------ED
Project2 SD-----------------ED

This is similar style as a MS Project timeline, but all of the data is in an Access database. We may need this either in a calendar year format or in the above fiscal year format.

I have been playing with it a bit, but can't seem to have each of the 2 date fields list under the appropriate month.
Any ideas?

 

[fneily]

I'm sure this will be hard to visualize, but I'll try to convey what I did in words. You'll be creating a timeline going left to right.

You'll find some redundant and/or extra coding in this. The reason is that this was created over three months and the client kept changing his mind, adding new conditions, etc. and I didn't feel like rewriting everything.

The code shown is for only one bar. My project had four bars being present in one record. So I'm showing you just a part. You'll get the idea from it. Some codes required words instead of a colored bar.

In the Page Header, over the month headings, I created an Invisible textbox whose width was nearly as long as the page. The name of the box in the code is called
Me!boxTimeLine. It represents a year. You'll see that I divided this width by 365 to break it into days. You'll have to change the distance from the left margin of the page to the left border of the texbox into twips from inches. There's 1440 twips to the inch.

For the month headings in the Page Header section, I created textboxes. The first was called txtMth0 with Control Source =Now(). Then the next textboxes had =DateAdd("m",2,[txtMth0]) as the Control Source, changing the second parameter to create the neccessary month. This allowed the months to automatically update every new month, so you always saw a year worth of months on the chart.

To draw the bars, I created a Label. In the code, the name is Modal.
All measurements are in twips. You'll see a +5 on some lines. I add to add 5 twips to get things lined up evenly, so it's a fudge factor.
Notice the code goes on the Format event of the Detail bar in Design view.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim lngDuration As Long 'days of tour
Dim lngDuration1 As Long
Dim lngDuration2 As Long
Dim lngDuration3 As Long
Dim lngStart As Long 'start date of tour
Dim lngStart1 As Long
Dim lngStart2 As Long
Dim lngStart3 As Long
Dim lngStartA As Long
Dim lngLMarg As Long
Dim dblFactor As Double
Dim holda As Long
Dim holdb As Long
'put a line control in your page header that starts 1/1 and goes to 12/31
lngLMarg = Me!boxTimeLine.left
dblFactor = Me!boxTimeLine.Width / 365
lngStart = 0

If IsNull(Me![Modal_Clearance_Table.ClearanceDate]) Then
lngDuration = 0
Me![Modal].BackStyle = 0
Me![Modal].Caption = ""
Me![Modal].BorderStyle = 0
Me![Modal].BorderWidth = 0
Else
Me![Modal].BackStyle = 1
If Me![Modal_Clearance_Table.Code] = "MCL" Then
Me![Modal].BackColor = 16777215
Me![Modal].SpecialEffect = 0
Me![Modal].BorderStyle = 0
Me![Modal].Caption = " Cleared"
Me![Modal].ForeColor = 4259584
GoTo jump
ElseIf Me![Modal_Clearance_Table.Code] = "MCR" Then
Me![Modal].BackColor = 16777215
Me![Modal].SpecialEffect = 0
Me![Modal].BorderStyle = 0
Me![Modal].Caption = " Cleared With Restrictions"
Me![Modal].ForeColor = 4259584
GoTo jump
ElseIf Me![Modal_Clearance_Table.Code] = "MCX" Then
Me![Modal].BackColor = 16777215
Me![Modal].SpecialEffect = 0
Me![Modal].BorderStyle = 0
Me![Modal].Caption = " Cleared - Exempt"
Me![Modal].ForeColor = 4259584
GoTo jump
Else
Me![Modal].BackColor = 8453888
Me![Modal].SpecialEffect = 4
Me![Modal].BackStyle = 1
Me![Modal].BorderWidth = 3
Me![Modal].BorderColor = 0
Me![Modal].BorderStyle = 1
Me![Modal].Caption = ""
End If
If Me![Date_of_Receipt] > Date Then
lngDuration = DateDiff("d", Me![Date_of_Receipt], Me![Modal_Clearance_Table.ClearanceDate]) + 5
lngStart = DateDiff("d", Date, Me![Date_of_Receipt])
ElseIf Me![Modal_Clearance_Table.ClearanceDate] > Date Then
lngDuration = DateDiff("d", Date, Me![Modal_Clearance_Table.ClearanceDate]) + 5
lngStart = 0
Else
If (Me![Modal_Clearance_Table.Code] = "MIP") Or (Me![Modal_Clearance_Table.Code] = "MUR") Then
Me![Modal].BackStyle = 1
Me![Modal].BackColor = 16777215
Me![Modal].SpecialEffect = 0
Me![Modal].BorderStyle = 0
Me![Modal].Caption = " EXPIRED"
Me![Modal].ForeColor = 0
lngDuration = 30
lngStart = 0
Else
jump: lngDuration = 65
lngStart = 0
Me![Modal].SpecialEffect = 0
Me![Modal].BackStyle = 0
Me![Modal].BorderStyle = 0
Me![Modal].BorderWidth = 0
End If
End If
End If


Me![Modal].Width = 10 'avoid the positioning error
Me![Modal].left = (lngStart * dblFactor) + lngLMarg
holda = (lngDuration * dblFactor)
If holda > 12200 Then
Me!Modal.Width = 12200
Else
Me![Modal].Width = (lngDuration * dblFactor)
End If

End Sub

 

[Vittles]

Thanks for your response!

I was wondering about doing a visible and backcolor property variation of coding instead where there would be 12 textboxes (12 months) across a page and then based on the value of the start date or end date I would make the box visible and contain a date, colored in to make the bar or invisible (if the date was before or after the month).

I can see I am going to have to play some more to see how the If...then...else will work. Overall the report would be fairly simple, but the coding would have to be fairly extensive. The table could have 50+ tasks in a year, but otherwise has just one start and end date per task. An added element would be to color code the bar based on what person was responsible for it.

Any other ideas are welcome as I start to play with this...

 

[dhookom]

There are sample calendar reports including a timeline as you have described at

http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Vittles]

Thanks Duane! I will have to play with that & see how it works with my data. Trying to learn visual basic as I set these up, so examples like that are great!