'From Report rptEmpTimeSheet
'This technique shows using a Table with a record '<ALL>' 
'AC 21/1/2001


Private Sub Report_Open(Cancel As Integer)

'Since the report NEEDS the form opened first.
'The idea is, if the open the report from the database window then the report calls the form. Not the form calling the report.

DoCmd.OpenForm frm, , , , , acDialog
'All this code is suspended until the user chooses OK or closes the form

If Not IsLoaded(frm) Then
    DoCmd.CancelEvent
    Exit Sub    'They closed the form
Else
    'Apply the filter
    sql = ""
    If Forms(frm)!ctlEmpIDRpt <> "<ALL>" Then
        sql = "EmpID = '" & Forms(frm)!ctlEmpIDRpt & "'"
    End If
    
    If Forms(frm)!ctlCategoryID <> "<ALL>" Then
        If sql <> "" Then sql = sql & " and "
        sql = sql & "CategoryID = '" & Forms(frm)!ctlCategoryID & "'"
    End If

    If Forms(frm)!ctlReportOption = 1 Then
        'N/A
    ElseIf Forms(frm)!ctlReportOption = 2 Then
        'A date range was choosen
        If sql <> "" Then sql = sql & " and "
        sql = sql & "DateCreated between #" & Format(Forms(frm)!ctlDateStart, "mm/dd/yyyy") & "# and #" & Format(Forms(frm)!ctlDateEnd, "mm/dd/yyyy") & " 23:59:59pm #"
    ElseIf Forms(frm)!ctlReportOption = 3 Then
        'A day was choosen
        If sql <> "" Then sql = sql & " and "
        sql = sql & "DateCreated = #" & Format(Forms(frm)!ctlDateStart, "mm/dd/yyyy") & "#"
    Else
        MsgBox "Logic Error"
    End If

    '="For the period " & Format([Forms]![frmrptEmpTimeSheet]![ctlDateStart],"Long Date") & " To " & Format([Forms]![frmrptEmpTimeSheet]![ctlDateTo],"Long Date")
    lblSQL.Caption = sql
    'MsgBox sql
    Me.Filter = sql
    Me.FilterOn = True
End If

End Sub