Quote Originally Posted by jonh View Post
Unless it's global, UPRmonthly isn't set.


Do you know what a query is?

Hey John. Thanks for that, I was about to post that I figured that out. I was trying to do this using a query, however I was unable to loop the query so as not to hardcode for each month (The number of months depends on user input). The code I tried using querydef is provided below, which only creates one column which represents the last calculation in the loop(obviously because previous queries get overwritten instead of adding on). Your comments on how to make this work are more than appreciated.

Private Sub Calculate_Click()
Dim db As Database
Dim x As Integer
Dim y As Integer
Dim WPmonthly As String ' field name for monthly written premium
Dim UPRmonthly As String ' field name for monthly unearned premium
Dim EPmonthly As String ' field name for monthly earned premium
Dim runningDate As Date
Dim useDateLower As Date
Dim useDateUpper As Date
Dim qry As dao.QueryDef


Months = Me.YearsBack * 12 + Month(Me.ValDate)


If Me.Period = "monthly" Then


    Set db = CurrentDb


    Set qry = CurrentDb.CreateQueryDef("MyQuery")
    Debug.Print qry.SQL ' shows the SQL from MyQuery


    For x = 1 To Months


    runningDate = Format(DateAdd("m", -x + 1, Me.ValDate), "mm yyyy")
    useDateLower = runningDate
    useDateUpper = Format(DateAdd("m", -x + 2, Me.ValDate), "mm yyyy")
    WPmonthly = "WP M" & Month(runningDate) & " " & Year(runningDate)
    EPmonthly = "EP M" & Month(runningDate) & " " & Year(runningDate)
    UPRmonthly = "UPR M" & Month(runningDate) & " " & Year(runningDate)
    qry.SQL = "SELECT IIf([tblEPdata]![IssueDate]>" & useDateLower & ",IIf([tblEPdata]![IssueDate]<" & useDateUpper & ",[tblEPdata]![GrossPremium])) AS " & WPmonthly & " FROM tblEPdata;"


    Next


    qry.Close


End If
end sub