Timeline Report - Null Value Coding issue

[Vittles]

Thanks to Duane Hookom's example database I was able to set up a simple timeline report that worked great.

However, when I modified the code so that I would use it for a series of dates attached to one project (each project can have up to 3 spans using 6 total data fields from an external source), the code doesn't work when the values for the dates are 'null' and I think I am now going in a circle as far as testing different adjustments.

Here is the modified code I am using - I have taken out the 'If' lines to get it to work for projects with all dates filled in - but it gives an idea of what I am trying to do. When I leave those lines in the project title is listed, but doesn't position itself where it should and isn't the correct color - so I am guessing that the code isn't working at all.


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim lngStart1 As Long 'start date of review process
Dim lngDuration1 As Long 'days of review process
Dim lngStart2 As Long 'start date of review process
Dim lngDuration2 As Long 'days of review process
Dim lngStart3 As Long 'start date of review process
Dim lngDuration3 As Long 'days of review process
Dim lngLMarg As Long
Dim dblFactor As Double

lngLMarg = Me.boxTimeLine.Left
dblFactor = Me.boxTimeLine.Width / 520

'If Me.Receipt_Date1_Adjusted <> Null Then
lngStart1 = DateDiff("d", [Forms]![Date Span]![Start Date], Me.Receipt_Date1_Adjusted)
lngDuration1 = DateDiff("d", Me.Receipt_Date1_Adjusted, Me.Review_Date1_Adjusted)
Me.TimelineBar.BackColor = Me.DivisionColor
Me.TimelineBar.Width = 10 'to avoid the positioning error
Me.TimelineBar.Left = (lngStart1 * dblFactor) + lngLMarg
Me.TimelineBar.Width = (lngDuration1 * dblFactor)
'End If

'If Me.Receipt_Date2_Adjusted <> Null Then
lngStart2 = DateDiff("d", [Forms]![Date Span]![Start Date], Me.Receipt_Date2_Adjusted)
lngDuration2 = DateDiff("d", Me.Receipt_Date2_Adjusted, Me.Review_Date2_Adjusted)
Me.TimelineBar2.BackColor = Me.DivisionColor
Me.TimelineBar2.Width = 10 'to avoid the positioning error
Me.TimelineBar2.Left = (lngStart2 * dblFactor) + lngLMarg
Me.TimelineBar2.Width = (lngDuration2 * dblFactor)
'End If

'If Me.Receipt_Date3_Adjusted <> Null Then
lngStart3 = DateDiff("d", [Forms]![Date Span]![Start Date], Me.Receipt_Date3_Adjusted)
lngDuration3 = DateDiff("d", Me.Receipt_Date3_Adjusted, Me.Review_Date3_Adjusted)
Me.TimelineBar3.BackColor = Me.DivisionColor
Me.TimelineBar3.Width = 10 'to avoid the positioning error
Me.TimelineBar3.Left = (lngStart3 * dblFactor) + lngLMarg
Me.TimelineBar3.Width = (lngDuration3 * dblFactor)
'End If

Me.MoveLayout = False
End Sub

I have tried adding an 'Else' coding line, but that doesn't help. Any ideas would be great!

 

[CautionMP]

Did you try [tt]If IsNull([Field]) Then[/tt] for your if statements instead of [tt]If [Field] <> Null Then[/tt]?

CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[Vittles]

Thanks!

I did make that change, as seen in the revised section code below (in case anyone else has this issue).

In order for the report to not list the other timelinebars at all, I also had to make the 3 timelinebar text boxes contain an IIF(IsNull(Receipt Date), Null, [Project Title]) formula and set their 'can shrink' property to Yes. For some reason adding an 'Else' statement in the coding didn't work, but for now the report looks like it is working well so I will try working with that when I have more time...

If Not (IsNull(Me.Receipt_Date1_Adjusted)) Then
lngStart1 = DateDiff("d", [Forms]![Date Span]![Start Date], Me.Receipt_Date1_Adjusted)
lngDuration1 = DateDiff("d", Me.Receipt_Date1_Adjusted, Me.Review_Date1_Adjusted)
Me.TimelineBar.BackColor = Me.DivisionColor
Me.TimelineBar.Width = 10 'to avoid the positioning error
Me.TimelineBar.Left = (lngStart1 * dblFactor) + lngLMarg
Me.TimelineBar.Width = (lngDuration1 * dblFactor)
End If

If Not IsNull(Me.Receipt_Date2_Adjusted) Then
lngStart2 = DateDiff("d", [Forms]![Date Span]![Start Date], Me.Receipt_Date2_Adjusted)
lngDuration2 = DateDiff("d", Me.Receipt_Date2_Adjusted, Me.Review_Date2_Adjusted)
Me.TimelineBar2.BackColor = Me.DivisionColor
Me.TimelineBar2.Width = 10 'to avoid the positioning error
Me.TimelineBar2.Left = (lngStart2 * dblFactor) + lngLMarg
Me.TimelineBar2.Width = (lngDuration2 * dblFactor)
End If


If Not IsNull(Me.Receipt_Date3_Adjusted) Then
lngStart3 = DateDiff("d", [Forms]![Date Span]![Start Date], Me.Receipt_Date3_Adjusted)
lngDuration3 = DateDiff("d", Me.Receipt_Date3_Adjusted, Me.Review_Date3_Adjusted)
Me.TimelineBar3.BackColor = Me.DivisionColor
Me.TimelineBar3.Width = 10 'to avoid the positioning error
Me.TimelineBar3.Left = (lngStart3 * dblFactor) + lngLMarg
Me.TimelineBar3.Width = (lngDuration3 * dblFactor)
End If

Thanks again!

 

[CautionMP]

Glad it helped.

FYI: You couldreplace the [tt]Iif()[/tt] with [tt]Nz()[/tt].

CMP

(GMT-07:00) Mountain Time (US & Canada)