How can you append a field to a table using a variable as the field name.

i.e.
dim Year1 as string
dim Year2 as string

Year1 = 2006
Year2 = 2007

With tdfNew
.Fields.Append .CreateField("WeekNo", dbLong)
.Fields.Append .CreateField(Year1, dbDouble)
.Fields.Append .CreateField(Year2, dbDouble)
End With

Creating these field would create a repeating group and denormalize a database. This should not be done.

I agree with William, but if you are determined to do this, you would need to change your code to this:

Year1 = "2006"
Year2 = "2007"