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.
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.
Sir
Any update or further advise please?
ok I think I've got it
Check the line I've added in red to the vba format codeCode: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
And on the properties sheet for the report make the default view be printpreview
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.
Hi
It is achieved by changing the below fields which use to set the timeline frame:
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.Code: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
Thanks for your time and support.