I am working on a report that will, through a form, allow multiple filters and a sort. I would like the title of the report to reflect the selections made for the filters & sort.
The report can be filtered by 3 fields: Product Type, Deliverables Received (yes/no) and Project Manager.
It can be sorted by: Contract number, Product Type, Contractor, TO Begin Date, TO End Date & Date Product Received
I have gotten some elements to work (Those where DeliverablesReceived have been selected with and without sorting) but am running into issues with the rest.
Public Function InsertTitle(strReportName As String) As String
Dim strProduct As String
If strReportName = "rptMasterDeliverables" Then
If strField = "" Then
InsertTitle = "All Deliverables Sorted by Project Manager"
ElseIf Forms!fdlgMasterDeliverables!cboDeliverableReceived Like "No" Then
InsertTitle = "Outstanding Deliverables Sorted by " & Left(strField, InStr(1, strField, ",") - 1) & ""
ElseIf Forms!fdlgMasterDeliverables!cboDeliverableReceived Like "Yes" Then
InsertTitle = "Received Deliverables Sorted by " & Left(strField, InStr(1, strField, ",") - 1) & ""
ElseIf InStr(1, strField, ",") = 0 Then
InsertTitle = "All Deliverables Sorted by " & strField & ""
The above all work well, I run into problems with the following
ElseIf Forms!fdlgMasterDeliverables!cboDeliverableReceived Like "Yes" And Left(strField, 1) = "," Then
InsertTitle = "Received Deliverables sorted by Project Manager"
ElseIf Left(strField, 1) = "," And Forms!fdlgMasterDeliverables!cboDeliverableReceived Like "No" Then 'cboReceived
InsertTitle = "Outstanding Deliverables sorted by Project Manager"
ElseIf InStr(1, strProduct, ",") = 0 And Forms!fdlgMasterDeliverables!cboDeliverableReceived Like "No" Then
InsertTitle = "" & Mid([strProduct], 2, Len([strProduct])) & " Deliverables"
ElseIf Left(strField, 1) = "*" And InStr(1, strField, ",") = 0 Then
InsertTitle = "All " & Mid([strField], 2, Len([strField])) & " Deliverables sorted by " & strField & ""
ElseIf Left(strField, 2) = "* " And Forms!fdlgMasterDeliverables!cboDeliverableReceived Like "Yes" Then
InsertTitle = "Received " & Mid([strField], 3, Len([strField])) & " Deliverables sorted by Project Manager"
The code for opening the report is
Private Sub Report_Open(Cancel As Integer)
Dim strProducts As String
strField = ""
If CurrentProject.AllForms("fdlgMasterDeliverables").IsLoaded Then
If Not IsNull(Forms!fdlgMasterDeliverables!cboSort) Then
Me.Report.GroupLevel(0).ControlSource = Forms!fdlgMasterDeliverables!cboSort.Column(0)
strField = Forms!fdlgMasterDeliverables!cboSort.Column(1)
If Not IsNull(Forms!fdlgMasterDeliverables!cboDeliverableReceived) Then
strField = strField & "," & Forms!fdlgMasterDeliverables!cboDeliverableReceived.Column(1)
End If
ElseIf Not IsNull(Forms!fdlgMasterDeliverables!cboDeliverableReceived) Then
strField = "," & Forms!fdlgMasterDeliverables!cboDeliverableReceived.Column(1)
End If
If Not IsNull(Forms!fdlgMasterDeliverables!cboProductType) Then
strProduct = Forms!fdlgMasterDeliverables!cboProductType.Column(1)
End If
I have considered going to a Case statement but have no idea where to begin. Any insight into what is going wrong would be greatly appreciated
Aucun commentaire:
Enregistrer un commentaire