field expression syntax error

[lkzkhan]

Depending on some inputs from the users in a user form, I am building code to automatically create calculated columns based on defined criteria. However, I am getting a syntax error (run time error 3125, " is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long. Error points to tdf.fields.append fld.) in the field expression, code provided below. Just FYI the inputs are:

me.yearsback= 0.5
me.valdate = 5/31/2016
me.period = "monthly"

Private Sub Calculate_Click()
Dim db As Database
Dim rs As Recordset
Dim x As Integer
Dim Months As Integer
Dim WPmonthly As String ' field name for monthly written premium
Dim runningDate As Date
Dim useDateLower As Date
Dim useDateUpper As Date
Dim tdf As dao.TableDef
Dim fld As dao.Field2


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

If Me.Period = "monthly" Then

Set db = CurrentDb
Set tdf = db.TableDefs("tblEPdata")

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)
Set fld = tdf.CreateField(UPRmonthly)
fld.Expression = "iif([issuedate]<#" & useDateUpper & "#,iif([issuedate]>=#" & useDateLower & "#,[grossPremium]))" ' output gross premium if issue date is between usedateupper and usedatelower, otherwise 0
tdf.Fields.Append fld
Next

MsgBox "added"

End If

End Sub

[jonh]

Unless it's global, UPRmonthly isn't set.


Do you know what a query is?

[lkzkhan]

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

[jonh]

You need to build up the field list as string and then build the sql from that like ...

Private Sub Calculate_Click()
    
    ValDate = #1/1/2002#
    
    runningDate = ValDate
    YearsBack = 2


    Months = YearsBack * 12 + Month(runningDate)
     
    'If Me.Period = "monthly" Then
         
        Set db = CurrentDb
        Set tdf = db.TableDefs("tblEPdata")


        For x = 1 To Months
            If Len(s) Then s = s & "," & vbNewLine
            s = s & mnthfld(runningDate, x)
        Next
        
        SQL = Replace("select % from tblEPdata", "%", s)
        
        Debug.Print SQL
        CurrentDb.QueryDefs("query1").SQL = SQL
        DoCmd.OpenQuery "query1"
    'End If
End Sub


Private Function mnthfld(dt, mno) As String
    useDateLower = dt
    useDateUpper = DateAdd("m", -mno + 2, dt)
    mnthfld = "IIf([issuedate] Between #" & Format(useDateLower, "mm/dd/yyyy") & "# And #" & _
        Format(useDateUpper, "mm/dd/yyyy") & "#,[grosspremium],0) as 'WP M" & _
        Format(useDateUpper, "mm yyyy") & "'"
End Function

[SamT]

me.yearsback= 0.5
me.valdate = 5/31/2016
me.period = "monthly"

Thos look like UserForm Control Values, which are always Strings. The VBE Compiler is very "smart" and forgiving, but I always explicitly convert them before use.

Dim yearsback As Double
Dim valdate as Date

yearsback = CDbl(Me.yearsback
valdate = CDate(Me.ValDate)

In the interest of speed add

Dim Period as String
Period = Me.period

To make your code handle many types of periods add

Dim NumPeriods as Long 'Replaces months

NumPeriods = GetPeriods

Private Function GetPeriods(Period As String, YearsBack As Double, ValDate As Date)

If Period = "monthly" Then
GetPeriods  = YearsBack * 12 + Month(ValDate) 
     
ElseIf Period = "yearly" then
GetPeriods = YearsBack + Year(ValDate)

ElseIf Period = "quarterly" then
'Etc
End If
End Function


Private Function GetRunningDate(Period As String, ValDate As Date) As Date

If Period  = "monthly" then 
GetRunningDate= Format(DateAdd("m", -x + 1, Me.ValDate), "mm yyyy")

ElseIf Period = "yearly" Then
'Etc

ElseIf Period = "quarterly" Then
'etc
End If
end Function

If you repeat this style of using small easily written and proofed functions for each of the period dependent variables in the query string, your loop can handle all possible periods.

        For x = 1 To Periods
            runningDate = GetrunningDate(Period, ValDate) 
            useDateLower = runningDate 
            useDateUpper = GetuseDateUpper(Period, ValDate) 
            WP = GetWp(RunningDate) 
            'EP = GetEP(runningDate 'This variable is not used in your query
            'UPR = GetUPR(RunningDate) 'This variable is not used in your query

            qry.SQL = "SELECT IIf([tblEPdata]![IssueDate]>" & useDateLower & ",IIf([tblEPdata]![IssueDate]<" & useDateUpper & ",[tblEPdata]![GrossPremium])) AS " & WP & " FROM tblEPdata;" 
        Next

[lkzkhan]

Much appreciated John and Sam.

Using most of the code provided by John, if I change the function to the one below I get an error "Extra ) in query expression '0)'". Following is the query expression:

mnthfld = "IIf([issuedate] <= #" & useDateUpper & "#," & _
    "IIf([expirydate] <= #" & useDateUpper & "#, 0, IIf([expirydate <=#" & useDateUpper & "#," & _
    "([expirydate]-#" & useDateUpper & "#+1)/([expirydate]-[effectivedate]+1),[grosspremium]),0),0) as 'EP M" & _
    Format(useDateLower, "mm yyyy") & "'

My hunch is the use of brackets in the calculations is something I'm getting wrong. Following is a debug print output on one iteration:

IIf([issuedate] <= #11/30/2015#,IIf([expirydate] <= #11/30/2015#, 0, IIf([expirydate <=#11/30/2015#,([expirydate]-#11/30/2015#+1)/([expirydate]-[effectivedate]+1),[grosspremium]),0,0)) as [EP M11 2015]

Any help is appreciated.

[lkzkhan]

Found the mistake. It was a silly "]" missing on the expirydate.