Hi all.

This is an FYI for those of you (and I've seen a few posts on this general topic) who are looking for a way to make a columnar report breaking something down by month ... THIS CODE WORKS! I'm not looking for a solution ... Just want you to see how I did it.

Fragment:
Code:
    ' Retrieve the Cash Payments
'SELECT [Work Orders II].ContactID, CustomerContacts.[First name], CustomerContacts.MI, CustomerContacts.[Last name], Payments.LaborPortion, Payments.PurchasesPortion, Payments.PaymentAmount, Payments.ReceivedDate
'FROM ([Work Orders II] INNER JOIN CustomerContacts ON [Work Orders II].ContactID = CustomerContacts.ContactID) INNER JOIN Payments ON [Work Orders II].WorkOrderNumber = Payments.WorkOrderNumber;
    SQLString = "SELECT [Work Orders II].ContactID, CustomerContacts.[First name] & iif(CustomerContacts.MI & '' <> '', ' ' & CustomerContacts.MI & '. ',' ') & CustomerContacts.[Last name] AS CustomerName, Payments.LaborPortion, Payments.PurchasesPortion, Payments.PaymentAmount, Payments.ReceivedDate"
    SQLString = SQLString & " FROM ([Work Orders II] INNER JOIN CustomerContacts ON [Work Orders II].ContactID = CustomerContacts.ContactID) INNER JOIN Payments ON [Work Orders II].WorkOrderNumber = Payments.WorkOrderNumber"
    SQLString = SQLString & " WHERE (CInt(DatePart('yyyy',Payments.ReceivedDate))=" & RptYear & ");"
    MyRecSet.Open SQLString, MyConnect
    If MyRecSet.BOF = False Then
        MyRecSet.MoveFirst
        SQLString = "SELECT * FROM " & RptTbl & " WHERE (1=0);"
        TrgRecSet.Open SQLString, FormsConnect
        While MyRecSet.EOF = False
            TrgRecSet.AddNew
            ' Contact ID
            TrgRecSet.Fields(0).Value = MyRecSet.Fields(0).Value
            ' Customer Name
            TrgRecSet.Fields(1).Value = MyRecSet.Fields(1).Value
            ' Zero out the record ...
            For lcv = 2 To 49
                TrgRecSet.Fields(lcv).Value = 0
            Next
            
            BillMonth = DatePart("m", MyRecSet.Fields(5).Value)
            ' Labor Amount
            If MyRecSet.Fields(2).Value <> 0 Then
                TrgRecSet.Fields(BillMonth + 1).Value = MyRecSet.Fields(2).Value
                If BillMonth < 4 Then TrgRecSet.Fields(14).Value = MyRecSet.Fields(2).Value
                If BillMonth < 7 Then TrgRecSet.Fields(15).Value = MyRecSet.Fields(2).Value
                If BillMonth < 10 Then TrgRecSet.Fields(16).Value = MyRecSet.Fields(2).Value
                TrgRecSet.Fields(17).Value = MyRecSet.Fields(2).Value
            End If
            
            ' Purchases Amount
            If MyRecSet.Fields(3).Value <> 0 Then
                TrgRecSet.Fields(BillMonth + 17).Value = MyRecSet.Fields(3).Value
                If BillMonth < 4 Then TrgRecSet.Fields(30).Value = MyRecSet.Fields(3).Value
                If BillMonth < 7 Then TrgRecSet.Fields(31).Value = MyRecSet.Fields(3).Value
                If BillMonth < 10 Then TrgRecSet.Fields(32).Value = MyRecSet.Fields(3).Value
                TrgRecSet.Fields(33).Value = MyRecSet.Fields(3).Value
            End If
            
            ' Total Billed
            If MyRecSet.Fields(4).Value <> 0 Then
                TrgRecSet.Fields(BillMonth + 33).Value = MyRecSet.Fields(4).Value
                If BillMonth < 4 Then TrgRecSet.Fields(46).Value = MyRecSet.Fields(4).Value
                If BillMonth < 7 Then TrgRecSet.Fields(47).Value = MyRecSet.Fields(4).Value
                If BillMonth < 10 Then TrgRecSet.Fields(48).Value = MyRecSet.Fields(4).Value
                TrgRecSet.Fields(49).Value = MyRecSet.Fields(4).Value
            End If
            TrgRecSet.Update
            MyRecSet.MoveNext
        Wend
        TrgRecSet.Close
    End If
    MyRecSet.Close