field expression syntax error
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"
Code:
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