Adding a field to a table in Access

[Martina]

I have created the code below to add a new column to a table each month. This may not be the best database design but it meets our needs for now.

However I am having difficultly with the code below. The CreateField Function is unable to accept the parameter periodDate. Any Suggestions on this would be apprerciated




Function DateField() As Long

Dim colFullName As Object
Dim dbsCurrent As Object
Dim yearInt As Integer
Dim monthInt As Integer
Dim table1 As Object


Set dbsCurrent = CurrentDb
Set table1 = CurrentDb.CreateTableDef("103TblCustomerBalancesCombined")

yearInt = Year(Date)
monthInt = Month(Date) - 1

If monthInt = 0 Then
periodDate = CLng(yearInt - 1 & 12)
Exit Function
End If

If monthInt < 10 Then
periodDate = CLng(yearInt & "0" & monthInt)
Else
periodDate = CLng(yearInt & "" & monthInt)
End If

Set colFullName = table1.CreateField(periodDate, DB_TEXT)
table1.Fields.Append colFullName


End Function

 

[ByteMyzer]

I recommend doing some research on crosstab queries. You should not have to add a new field for each month.