Compile Error (Syntax Error)

[cap.zadi]

Yeah it is.

Kindly check the previously given db's report. You will see the timeline (textfields) are from jan to Dec but the status bar followed the minstartdate and maxenddate as per coding.

[cap.zadi]

Sir

Any update or further advise please?

[andy49]

ok I think I've got it

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim intStartDayDiff As Integer
Dim intDayDiff As Integer
Dim sngFactor As Single
  
  On Error Resume Next

mdatEarliest = #1/1/2017#

  Me.ScaleMode = 1 'Twips
  'sngFactor = Me.boxMaxDays.Width / mintDayDiff
  sngFactor = Me.boxMaxDays.Width / 365
  If Not IsNull(Me.StartDate) And Not IsNull(Me.EndDate) Then
    Me.boxGrowForDate.Visible = True
    Me.lblTotalDays.Visible = True
    intStartDayDiff = Abs(DateDiff("d", Me.StartDate, mdatEarliest))
    intDayDiff = Abs(DateDiff("d", Me.EndDate, Me.StartDate))
  
    If intStartDayDiff = 0 Then intStartDayDiff = 1
    
    With Me.boxGrowForDate
      .Left = Me.boxMaxDays.Left + (intStartDayDiff * sngFactor)
      .Width = intDayDiff * sngFactor
    End With
    
    Me.lblTotalDays.Left = Me.boxGrowForDate.Left
    
    If intDayDiff > 1 Then Me.lblTotalDays.Caption = intDayDiff & " days" Else _
            Me.lblTotalDays.Caption = intDayDiff & " day"
    'Me.lblTotalDays.Caption = intDayDiff & " Day(s)"
  Else '
    Me.boxGrowForDate.Visible = False
    Me.lblTotalDays.Visible = False
  End If
End Sub

Check the line I've added in red to the vba format code

And on the properties sheet for the report make the default view be printpreview

[cap.zadi]

Thanks for the feedback.

This code did not give any error but almost gives the starting point same for all bars.

So finally it does not provide the required results.

[cap.zadi]

Hi

It is achieved by changing the below fields which use to set the timeline frame:

Private Sub Report_Open(Cancel As Integer)


Dim db As Database
Dim rs As Recordset
  
  Set db = CurrentDb
  Set rs = db.OpenRecordset("SELECT Min([SStartDate]) AS MinOfStartDate " _
                & " FROM QEAVS", dbOpenSnapshot)
  
  If rs.RecordCount > 0 Then
    mdatEarliest = rs!MinOfStartDate
  End If
  
Set rs = db.OpenRecordset("SELECT Max(IIf(IsDate([SEndDate]),CDate([SEndDate]),Null)) " _
                & "AS MaxOfEndDate FROM QEAVS", dbOpenSnapshot)
  
  If rs.RecordCount > 0 Then
    mdatLatest = rs!MaxOfEndDate
  End If
  
mintDayDiff = DateDiff("d", mdatEarliest, mdatLatest)
  
  Me.txtMinStartDate.Caption = Format(mdatEarliest, "dd/mm/yy")
  Me.txtMaxEndDate.Caption = Format(mdatLatest, "dd/mm/yy")
  
  Set rs = Nothing
  Set db = Nothing




End Sub

I think this is a great achievement bcz we are used to prepare such graphical leave reports in excel and its rare to find such customized staff leave schedule report in access.

Thanks for your time and support.