1024 Character Limit Error on a long expression

[bg18461]

I am trying this really long query expression to get quarterly data, but I run into a 1024 character limit error, can anyone help me with a work around

1ST QTR ENERGY MONTH 1-12: IIF ([MNTH1] LIKE "1/1/" & Year([MNTH1]),
[AKW1]+[NAKW1],IIF ([MNTH1] LIKE "2/1/" & Year([MNTH1]),[AKW2]+[AKW1]
+[NAKW2]+[NAKW1],IIF ([MNTH1] LIKE "3/1/" & Year([MNTH1]),[AKW3]+[AKW
2]+[AKW1]+[NAKW3]+[NAKW2]+[NAKW1],IIF ([MNTH1] LIKE "4/1/" & Year([MNTH1]),[AKW4]+[AKW3]+[AKW2]+[NAKW4]+[NAKW3]+[NAKW2],IIF
 ([MNTH1] LIKE "5/1/" & Year([MNTH1]),[AKW5]+[AKW4]+[AKW3]+[NAKW5]+
[NAKW4]+[NAKW3],IIF ([MNTH1] LIKE "6/1/" & Year([MNTH1]),[AKW6]+[AKW
5]+[AKW4]+[NAKW6]+[NAKW5]+[NAKW4],IIF ([MNTH1] LIKE "7/1/" & Year
([MNTH1]),[AKW7]+[AKW6]+[AKW5]+[NAKW7]+[NAKW6]+[NAKW5],IIF
 ([MNTH1] LIKE "8/1/" & Year([MNTH1]),[AKW8]+[AKW7]+[AKW6]+[NAKW8]+
[NAKW7]+[NAKW6],IIF ([MNTH1] LIKE "9/1/" & Year([MNTH1]),[AKW9]+[AKW
8]+[AKW7]+[NAKW9]+[NAKW8]+[NAKW7],IIF ([MNTH1] LIKE "10/1/" & Year
([MNTH1]),[AKW10]+[AKW9]+[AKW8]+[NAKW10]+[NAKW9]+[NAKW8],IIF
 ([MNTH1] LIKE "11/1/" & Year([MNTH1]),[AKW11]+[AKW10]+[AKW9]+[NAKW
11]+[NAKW10]+[NAKW9],IIF ([MNTH1] LIKE "12/1/" & Year([MNTH1]),[AKW
12]+[AKW11]+[AKW10]+[NAKW12]+[NAKW11]+[NAKW10],0))))))))))))

 

[bg18461]

Ok I rewrote it as a case statement in vba, can anyone help me verify this code will work in access and how to implement it into the query

Sub FirstQtrRevMth1to12()
 
Dim dCurrentDate As Date
Dim sDate As String
 
 
    dCurrentDate = [MNTH1]
    sDate = Month([MNTH1]) & "/1/" & Year([MNTH1])
 
    Select Case sDate
 
        Case "1/1/" & Year(dCurrentDate)
            iQtrOne = [DEBIT1]
        Case "2/1/" & Year(dCurrentDate)
            iQtrOne = [DEBIT2] + [DEBIT1]
        Case "3/1/" & Year(dCurrentDate)
            iQtrOne = [DEBIT3] + [DEBIT2] + [DEBIT1]
        Case "4/1/" & Year(dCurrentDate)
            iQtrOne = [DEBIT4] + [DEBIT3] + [DEBIT2]
        Case "5/1/" & Year(dCurrentDate)
            iQtrOne = [DEBIT5] + [DEBIT4] + [DEBIT3]
        Case "6/1/" & Year(dCurrentDate)
            iQtrOne = [DEBIT6] + [DEBIT5] + [DEBIT4]
        Case "7/1/" & Year(dCurrentDate)
            iQtrOne = [DEBIT7] + [DEBIT6] + [DEBIT5]
        Case "8/1/" & Year(dCurrentDate)
            iQtrOne = [DEBIT8] + [DEBIT7] + [DEBIT6]
        Case "9/1/" & Year(dCurrentDate)
            iQtrOne = [DEBIT9] + [DEBIT8] + [DEBIT7]
        Case "10/1/" & Year(dCurrentDate)
            iQtrOne = [DEBIT10] + [DEBIT9] + [DEBIT8]
        Case "11/1/" & Year(dCurrentDate)
            iQtrOne = [DEBIT11] + [DEBIT10] + [DEBIT9]
        Case "12/1/" & Year(dCurrentDate)
            iQtrOne = [DEBIT12] + [DEBIT11] + [DEBIT10]
        Case Else
            iQtrOne = 0
        
    End Select
    
    
End Sub

 

[bg18461]

i figured it out

 

[ByteMyzer]

Care to share?