I am renaming fields in a temp table using specified dates calculated on a form. I need the table field in my expression to be a variable since I have to change 10 fields with different dates. I can not get the correct syntax to replace ("LYWeek1")

CurrentDb.TableDefs("T7_Temp Dollars LYWeek").Fields("LYWeek1").Name = Datefield

("LYWeek1") is the field name and needs to be replaced with a variable.

Dim Weekend AS String
       Weekend = Forms!F_Date!txtEndDate.Value
So Weekend will be my variable in this case.

Dim Weekend AS String Weekend = Forms!F_Date!txtEndDate.Value CurrentDb.TableDefs("T7_Temp Dollars LYWeek").Fields(Weekend).Name = Datefield

the only thing you have to consider is that you must assign Weekend a value that represents the name of a field in your table, if Weekend assumes a value that doesn't represent a column table name this will result in a runtime error...

please give us more informations on table content, structure and meaning

____________________________
AndreaVB

Ooooops...sorry I thought you were trying to use a field name in a variable, instead you need to rename a field in a table...why on't you use an SQL statement, I don't know if it works for Access but surely it works for SQL server, just something like:

"ALTER TABLE [T7_Temp Dollars LYWeek] RENAME COLUMN LYWeek1 TO " & Weekend

...but I'm not suer this will work in Access...

I've found other people with the same problem in other forums and they suggest to use the same initial syntax you proposed:

CurrentDb.TableDefs("T7_Temp Dollars LYWeek").Fields("LYWeek1").Name = Datefield

be sure that Datefield is a string variable containing the new name for the column...they say this works for them

...another way could be creating a VIEW with new names for the columns:

SELECT a AS new_a_field_name,b as b_new_field_name FROM tablename

this will open a recordset based on table "tablename" with new names for "a" and "b" fields, creating this SQL statement dynamically in your code will allow you to have a recordset with the column names you want

...let me know

[Edited by admin on 17-11-2006 at 09:31 AM GMT]

____________________________
AndreaVB

Thanks for the responses

more info on table structure and meaning
The 10 fields that are to be renamed Weekend are populated with total units for that week.
Fields as follows
Cust = Text
LYWeek1 = Number
LYWeek2 and so on to LYWeek10

I want to query this table with another then export to Excel for end users to manipulate. However, the user will not know the Weekending dates specified for fiscal year. So I want the field to name to change and when exported to excel the field name will read as the Weekend date.

Could not get Access to take Alter Table command
Still trying other suggestion

CurrentDb.TableDefs("T7_Temp Dollars LYWeek").Fields(Weekend).Name = Datefield

(Weekend)  worked perfect.

Thanks for your help and time