Function FilterOptions() As String
Dim TimeCheck As Double
TimeCheck = TimeMilli
' TimeCheck = TimeMilli - TimeCheck
' Debug.Print "C1", Format(TimeCheck, "#,##0.000")
' TimeCheck = TimeMilli
Dim ClientNameSelected As String
Dim DirectorsNameSelected As String
Dim TaskCategorySelected As String
Dim TaskDateSelected As String
Dim RespAssSelected As String
Dim ResponsibilitySelected As String
Dim AssignedToSelected As String
Dim EarliestStartSelected As String
Dim ClientDeadlineSelected As String
Dim ExternalDeadlineSelected As String
Dim StatusSelected As String
Dim ProgressSelected As String
Dim WaitingForSelected As String
Dim BillableSelected As String
Dim InvoicedSelected As String
Dim ClientStatusSelected As String
Dim Ordering As String
Dim ClientDateOrdering As String
Dim ExternalDateOrdering As String
Dim ProgressOrder As String
Dim DateOverdue As Date
Dim Date1Week As Date
Dim Date2Weeks As Date
Dim Date3Weeks As Date
Dim Date4Weeks As Date
Dim Date5Weeks As Date
Dim Date6Weeks As Date
Dim Date7Weeks As Date
Dim Date8Weeks As Date
Dim Date9Weeks As Date
Dim Date10Weeks As Date
Dim Date11Weeks As Date
Dim Date12Weeks As Date
DateOverdue = Format(Date, "MM/DD/YYYY")
Date1Week = Format(DateAdd("d", 7, Date), "MM/DD/YYYY")
Date2Weeks = Format(DateAdd("d", 14, Date), "MM/DD/YYYY")
Date3Weeks = Format(DateAdd("d", 21, Date), "MM/DD/YYYY")
Date4Weeks = Format(DateAdd("d", 28, Date), "MM/DD/YYYY")
Date5Weeks = Format(DateAdd("d", 35, Date), "MM/DD/YYYY")
Date6Weeks = Format(DateAdd("d", 42, Date), "MM/DD/YYYY")
Date7Weeks = Format(DateAdd("d", 49, Date), "MM/DD/YYYY")
Date8Weeks = Format(DateAdd("d", 56, Date), "MM/DD/YYYY")
Date9Weeks = Format(DateAdd("d", 63, Date), "MM/DD/YYYY")
Date10Weeks = Format(DateAdd("d", 70, Date), "MM/DD/YYYY")
Date11Weeks = Format(DateAdd("d", 77, Date), "MM/DD/YYYY")
Date12Weeks = Format(DateAdd("d", 84, Date), "MM/DD/YYYY")
Dim g, h, i, j, k, l, m, n, o As String
' TimeCheck = TimeMilli - TimeCheck
' Debug.Print "C2", Format(TimeCheck, "#,##0.000")
' TimeCheck = TimeMilli
' Set the SQL string for ClientName
If (IsNull(ClientNameCombo.Value) Or ClientNameCombo.Value = "All") Then
ClientNameSelected = "1 = 1"
Else
ClientNameSelected = "BusinessName = '" & ClientNameCombo.Value & "'"
End If
' Set the SQL string for DirectorsName
If (IsNull(DirectorsNameCombo.Value) Or DirectorsNameCombo.Value = "All") Then
DirectorsNameSelected = "1 = 1"
Else
DirectorsNameSelected = "ClientName = '" & DirectorsNameCombo.Value & "'"
End If
' Set the SQL string for TaskCategory
If (IsNull(TaskCategoryCombo.Value) Or TaskCategoryCombo.Value = "All") Then
TaskCategorySelected = "1 = 1"
Else
TaskCategorySelected = "TaskCategory = '" & TaskCategoryCombo.Value & "'"
End If
' Set the SQL string for TaskDate
If (IsNull(TaskDateCombo.Value) Or TaskDateCombo.Value = "All" Or TaskDateCombo.Value = "Select date") Then
TaskDateSelected = "1 = 1"
Else
TaskDateSelected = "TaskDate = #" & Format(TaskDateCombo.Value, "DD-MMM-YY") & "#"
End If
' Set the SQL string for ResponsibilityAndAssignedTo
If (IsNull(RespAssCombo.Value) Or RespAssCombo.Value = "All") Then
RespAssSelected = "1 = 1"
Else
RespAssSelected = "(Responsibility = '" & RespAssCombo.Value & "' Or " & _
"AssignedTo = '" & RespAssCombo.Value & "' Or " & _
"WaitingFor = '" & RespAssCombo.Value & "')"
End If
' Set the SQL string for Responsibility
If (IsNull(ResponsibilityCombo.Value) Or ResponsibilityCombo.Value = "All") Then
ResponsibilitySelected = "1 = 1"
Else
ResponsibilitySelected = "Responsibility = '" & ResponsibilityCombo.Value & "'"
End If
' Set the SQL string for AssignedTo
If (IsNull(AssignedToCombo.Value) Or AssignedToCombo.Value = "All") Then
AssignedToSelected = "1 = 1"
Else
AssignedToSelected = "AssignedTo = '" & AssignedToCombo.Value & "'"
End If
' Set the SQL string for EarliestStart
If (IsNull(EarliestStartCombo.Value) Or EarliestStartCombo.Value = "All") Then
EarliestStartSelected = "1 = 1"
Else
If EarliestStartCombo.Value = "Ready" Then
EarliestStartSelected = "EarliestStartDate <= #" & Format(Date, "MM-DD-YY") & "#"
Else
End If
End If
' Set the SQL string for ClientDeadline
If (IsNull(ClientDeadlineCombo.Value) Or ClientDeadlineCombo.Value = "All") Then
ClientDeadlineSelected = "1 = 1"
Else
If ClientDeadlineCombo.Value = "Overdue" Then
ClientDeadlineSelected = "ClientDueDate <= #" & DateOverdue & "#"
Else
If ClientDeadlineCombo.Value = "1 week" Then
ClientDeadlineSelected = "ClientDueDate <= #" & Date1Week & "#"
Else
If ClientDeadlineCombo.Value = "2 weeks" Then
ClientDeadlineSelected = "ClientDueDate <= #" & Date2Weeks & "#"
Else
If ClientDeadlineCombo.Value = "4 weeks" Then
ClientDeadlineSelected = "ClientDueDate <= #" & Date4Weeks & "#"
Else
If ClientDeadlineCombo.Value = "8 weeks" Then
ClientDeadlineSelected = "ClientDueDate <= #" & Date8Weeks & "#"
Else
End If
End If
End If
End If
End If
End If
' Set the SQL string for ExternalDeadline
If (IsNull(ExternalDeadlineCombo.Value) Or ExternalDeadlineCombo.Value = "All") Then
ExternalDeadlineSelected = "1 = 1"
Else
If ExternalDeadlineCombo.Value = "Overdue" Then
ExternalDeadlineSelected = "ExternalDueDate <= #" & DateOverdue & "#"
Else
If ExternalDeadlineCombo.Value = "1 week" Then
ExternalDeadlineSelected = "ExternalDueDate <= #" & Date1Week & "#"
Else
If ExternalDeadlineCombo.Value = "2 weeks" Then
ExternalDeadlineSelected = "ExternalDueDate <= #" & Date2Weeks & "#"
Else
If ExternalDeadlineCombo.Value = "4 weeks" Then
ExternalDeadlineSelected = "ExternalDueDate <= #" & Date4Weeks & "#"
Else
If ExternalDeadlineCombo.Value = "8 weeks" Then
ExternalDeadlineSelected = "ExternalDueDate <= #" & Date8Weeks & "#"
Else
End If
End If
End If
End If
End If
End If
' Set the SQL string for Status
If (IsNull(StatusCombo.Value) Or StatusCombo.Value = "All") Then
StatusSelected = "1 = 1"
Else
StatusSelected = "Status = '" & StatusCombo.Value & "'"
End If
' Set the SQL string for Progress
If (IsNull(ProgressCombo.Value) Or ProgressCombo.Value = "All") Then
ProgressSelected = "1 = 1"
Else
ProgressSelected = "Progress = '" & ProgressCombo.Value & "'"
End If
' Set the SQL string for WaitingFor
If (IsNull(WaitingForCombo.Value) Or WaitingForCombo.Value = "All") Then
WaitingForSelected = "1 = 1"
Else
WaitingForSelected = "WaitingFor = '" & WaitingForCombo.Value & "'"
End If
' Set the SQL string for Billable
If (IsNull(BillableCombo.Value) Or BillableCombo.Value = "All") Then
BillableSelected = "1 = 1"
Else
If BillableCombo.Value = "Yes" Then
BillableSelected = "Billable = 'Yes'"
Else
BillableSelected = "Billable = 'No'"
End If
End If
' Set the SQL string for Invoiced
If (IsNull(InvoicedCombo.Value) Or InvoicedCombo.Value = "All") Then
InvoicedSelected = "1 = 1"
Else
InvoicedSelected = "Invoiced = '" & InvoicedCombo.Value & "'"
End If
' Set the SQL string for ClientStatusSelected
ClientStatusSelected = "NOT ClientStatus = 'Old client'"
' Set the ordering
ClientDateOrdering = "IIf(IsNull(ExternalDueDate),ClientDueDate,ExternalDueDate)"
ExternalDateOrdering = "IIf(IsNull(ClientDueDate),ExternalDueDate,ClientDueDate)"
' Ordering = "IIf(" & ExternalOrdering & "<" & ClientOrdering & "," & ExternalOrdering & "," & ClientOrdering & ")"
' TimeCheck = TimeMilli - TimeCheck
' Debug.Print "C3", Format(TimeCheck, "#,##0.000")
' TimeCheck = TimeMilli
Select Case Me.SortOptionGroup.Value
Case 1: Ordering = "ClientName ASC, TaskDate ASC, " & ExternalDateOrdering & " ASC"
Case 2: Ordering = "ClientName ASC, EarliestStartDate ASC, " & ExternalDateOrdering & " ASC"
Case 3: Ordering = "ClientName ASC, " & ExternalDateOrdering & " ASC"
Case 23: Ordering = "ClientName ASC, Progress ASC, " & ExternalDateOrdering & " ASC"
Case 24: Ordering = "ClientName ASC, Progress DESC, " & ExternalDateOrdering & " ASC"
Case 4: Ordering = "TaskCategory ASC, TaskDate ASC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 5: Ordering = "TaskCategory ASC, EarliestStartDate ASC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 6: Ordering = "TaskCategory ASC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 25: Ordering = "TaskCategory ASC, Progress ASC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 26: Ordering = "TaskCategory ASC, Progress DESC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 7: Ordering = "TaskDate ASC, EarliestStartDate ASC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 8: Ordering = "TaskDate ASC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 27: Ordering = "TaskDate ASC, Progress ASC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 28: Ordering = "TaskDate ASC, Progress DESC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 9: Ordering = "Responsibility ASC, TaskDate ASC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 10: Ordering = "Responsibility ASC, EarliestStartDate ASC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 11: Ordering = "Responsibility ASC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 29: Ordering = "Responsibility ASC, Progress ASC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 30: Ordering = "Responsibility ASC, Progress DESC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 12: Ordering = "EarliestStartDate ASC, TaskDate ASC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 13: Ordering = "EarliestStartDate ASC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 31: Ordering = "EarliestStartDate ASC, Progress ASC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 32: Ordering = "EarliestStartDate ASC, Progress DESC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 14: Ordering = "MGDueDate ASC, EarliestStartDate ASC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 15: Ordering = "MGDueDate ASC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 33: Ordering = "MGDueDate ASC, Progress ASC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 34: Ordering = "MGDueDate ASC, Progress DESC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 16: Ordering = ClientDateOrdering & " ASC, EarliestStartDate ASC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 17: Ordering = ClientDateOrdering & " ASC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 35: Ordering = ClientDateOrdering & " ASC, Progress ASC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 36: Ordering = ClientDateOrdering & " ASC, Progress DESC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 18: Ordering = ExternalDateOrdering & " ASC, TaskDate ASC, ClientName ASC"
Case 19: Ordering = ExternalDateOrdering & " ASC, EarliestStartDate ASC, ClientName ASC"
Case 37: Ordering = ExternalDateOrdering & " ASC, Progress ASC, ClientName ASC"
Case 38: Ordering = ExternalDateOrdering & " ASC, Progress DESC, ClientName ASC"
Case 20: Ordering = "Progress DESC, TaskDate ASC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 21: Ordering = "Progress DESC, EarliestStartDate ASC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 22: Ordering = "Progress DESC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 39: Ordering = "Progress ASC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 40: Ordering = "Progress DESC, " & ExternalDateOrdering & " ASC, ClientName ASC"
End Select
Me.RecordSource = _
"SELECT * " & _
"FROM WorkOutstanding " & _
"WHERE " & ClientNameSelected & " " & _
"AND " & DirectorsNameSelected & " " & _
"AND " & TaskCategorySelected & " " & _
"AND " & TaskDateSelected & " " & _
"AND " & RespAssSelected & " " & _
"AND " & ResponsibilitySelected & " " & _
"AND " & AssignedToSelected & " " & _
"AND " & WaitingForSelected & " " & _
"AND " & EarliestStartSelected & " " & _
"AND " & ClientDeadlineSelected & " " & _
"AND " & ExternalDeadlineSelected & " " & _
"AND " & StatusSelected & " " & _
"AND " & ProgressSelected & " " & _
"AND " & BillableSelected & " " & _
"AND " & InvoicedSelected & " " & _
"AND " & ClientStatusSelected & " " & _
"ORDER BY " & Ordering & " "
FilterOptions = Me.RecordSource
DoCmd.Requery
Me.Refresh
End Function