Swappiing Columns for fields problem

[Gordzilla]

I've inherited a database table that has hundreds of individual fields that
should have been created as two single fields with one of the fields
containing the values that is now the field name. The script below should
create a new table with only three fields and it does. My problem is that
it is populating the third field with the same value as the second instead
of getting the original field's value.
Any ideas?
Can't reinput as there are over 150 fields and over 6000 records.

Regards,

Gordzilla


Sample data
Current
Field
DNo 1997002 1998001 2004055 20030055 ......over 150 more
12344 0 999 56 0
11333 1 0 53 1

Required
Field
DNo EAC Hours
12344 1997002 0
12344 1998001 999
12344 2004055 56

What I get
Field
DNo EAC Hours
12344 1997002 1997002
12344 1998001 1998001
12344 2004055 2004055


Set tdf = db.TableDefs(mytable)
For Each fld In tdf.Fields
If Left(fld.Name, 1) = "1" Or Left(fld.Name, 1) = "2" Then '
loop trough all fields, but only if the field starts with 1 or 2

strSql = "INSERT INTO "
strSql = strSql & " tmp "
strSql = strSql & " ( fldDNo, fldEAC, fldEACHours ) "
strSql = strSql & " SELECT "
strSql = strSql & " DNo "
strSql = strSql & " , " & """" & fld.Name & """" & " AS EAC "
strSql = strSql & " , " & fld.Name & " AS EACHours"
strSql = strSql & " FROM " & mytable & " ; "

db.Execute strSql

 

[Kevin K. Sullivan]

I think you just need to change:

strSql = strSql & " , " & fld.Name & " AS EACHours"

to

strSql = strSql & " , [" & fld.Name & "] AS EACHours"
or
strSql = strSql & " , [" & mytable & "].[" & fld.Name & "] AS
EACHours"

------
Because the current fields are numeric, the value of the field name is
inserted into the table as a constant. Surrounding the numbers with square
brackets should identify them as a field name, which is evaluated properly.

HTH,

Kevin