mardi 19 mai 2015

Inserting title into Access report based on multiple conditions

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