How do I use varibale field names

I have a table which has a current month info and carries a 12 month rolling
history. At the end of each month i must copy the current month's data to
the relevant history field (ie. Jan for january, feb for february, etc). How
do i do this with a macro or with VBA. I am new to VBA. I currently edit an
update query at the end of each month, and manually type in the relevant
month in the field selection.

Greg wrote:
I have a table which has a current month info and carries a 12 month
rolling history. At the end of each month i must copy the current
month's data to the relevant history field (ie. Jan for january, feb
for february, etc). How do i do this with a macro or with VBA. I am
new to VBA. I currently edit an update query at the end of each
month, and manually type in the relevant month in the field selection.

The problem is the design of the table. The month is data and should be
stored in a column called HistoryMonth or something like that. This would
allow you to discontinue your timewasting practice of moving data around.

In essence, your "current month" table would become your history table,
simply by adding a column to store the month. If you wish to avoid deleting
data every month, you could also add a column to contain the year.

The alternative is to use VBA to prompt for the month and dynamically build
and execute a sql statement to move your data.

I'd rather not waste time going into details without hearing which path you
intend to take so I will end here.



--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

On Sat, 2 Jan 2010 08:36:01 -0800, Greg
wrote:

I have a table which has a current month info and carries a 12 month rolling
history. At the end of each month i must copy the current month's data to
the relevant history field (ie. Jan for january, feb for february, etc). How
do i do this with a macro or with VBA. I am new to VBA. I currently edit an
update query at the end of each month, and manually type in the relevant
month in the field selection.

I'm with Bob; your table structure is simply WRONG.

For one thing, you should not be storing the 12 month rolling history AT ALL.
Just store the data and use a Query to select the past twelve months.

--

John W. Vinson [MVP]