Compile Error (Syntax Error)

[cap.zadi]

Dears



Any help please?

Private Sub Report_Open(Cancel As Integer)

Dim db As Database
Dim rs As Recordset
  
  Set db = CurrentDb
  Set rs = db.OpenRecordset("SELECT Min([StartDate]) AS MinOfStartDate "
                & " FROM QEAVS", dbOpenSnapshot)
  
  If rs.RecordCount > 0 Then
    mdatEarliest = rs!MinOfStartDate
  End If
  
  Set rs = db.OpenRecordset("SELECT Max(IIf(IsDate([EndDate]),CDate([EndDate]),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, "mm/dd/yy")
  Me.txtMaxEndDate.Caption = Format(mdatLatest, "mm/dd/yy")
  
  Set rs = Nothing
  Set db = Nothing


End Sub

[andy49]

Not sure
Have you tried closing the first rs before using it again?

but wondered why not use dmin function instead of first rs.

[cap.zadi]

Hi

Thanks for the feedback

Attached, is the DB which is taken from forum and trying to use the same report format for Employees vacations schedule but having this error only while opening the report.

regards

[andy49]

If you put the Select........... statement in a query, does it return the values you want?

[cap.zadi]

Yeah this is working ok but when a copied into my DB report which is designed for the same way but getting this error.

[andy49]

If you put the Select........... statement in a query, does it return the values you want?

Otherwise without maybe putting your db on here it's really difficult as your code seems ok.

[cap.zadi]

Dear Sir

Thanks for your time.

I was not able to compress the file. So, i made a onedrive link for your review and guidance.

https://1drv.ms/u/s!AnATXf2TV3fdfK7XBGH5GZN8Wp0

[andy49]

Private Sub Report_Open(Cancel As Integer)

Dim db As Database
Dim rs As Recordset
  
  Set db = CurrentDb
  Set rs = db.OpenRecordset("SELECT Min([StartDate]) AS MinOfStartDate " _
                & " FROM QEAVS", dbOpenSnapshot)
  
  If rs.RecordCount > 0 Then
    mdatEarliest = rs!MinOfStartDate
  End If
  
Set rs = db.OpenRecordset("SELECT Max(IIf(IsDate([EndDate]),CDate([EndDate]),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, "mm/dd/yy")
  Me.txtMaxEndDate.Caption = Format(mdatLatest, "mm/dd/yy")
  
  Set rs = Nothing
  Set db = Nothing


End Sub

Try the red underscore?

[cap.zadi]

Sir

Fantastic and its working.

Just one more question. How to set up the timeline in 12 months.

As you can see in my report, the text label shows in months but as this code works starting from min and to max range.

Any advise please?

[andy49]

Do you mean changing the datediff function to "m" instead of "d". This would count months

[cap.zadi]

No Sir

I mean, if you see the timeline which is in months as heading and it should start from Jan till Dec not based on minstartdate or maxstartdate.

This bar shall be based on days as it is already.

[andy49]

I'm little unclear.

Do you mean that you'd like the bars to start at say March if they started in March?

Please give me an example?

[cap.zadi]

Well, Sorry i was not able to explain you.

The time line scale shall be based on current year starting from Jan till dec as now its 2017.

The status bar shall start and expand (days) based on this scale.

As report format is attached based on current db but the text scale is shown starting from Jan-Dec.

[cap.zadi]

So, i want to start the timeline from jan to dec not based on system selection from table fields (startdate & enddate)minstartdates or maxenddates as timeline but off course status bar shall grow between actual startdate and enddate as it is now.

[andy49]

This seems to be working in print preview?