Writing to a table with a variable as the field name

[WSC]

I am writing to a table within VBA code. The fields I need to write to are labeled 1 through 24 (for the hours of the day). I have a loop that returns a value per hour and I want to write the value for that hour to the proper hour column. Any suggestions?

 

[MarkK]

My suggestion is that hours of the day should not be columns. They are data and they should be in a column called 'hour'. By naming your columns 1-24 you create the problem of having to address column names dynamically. As you now observe, writing information this way is hard, and you'll find that retrieving information stored this way is equally hard.

Make a table that has columns something like...

ID
Date
Hour  (although this could be included in the date field)
Value

Writing to this table is easy, and retrieving and summarizing data for particular time periods and hours is also easy.

Cheers