Order field names after fields.append

[SusanV]

Hi all,

I've got all the code worked out to create tables from pivoted spreadsheets,
rename the fields, and then append similar fields accordingly, and now I
need to re-order the fields for output. I know - sounds like simply "set
your query for output by listing the fields in the order you want" BUT - the
field names will vary depending on the imported spreadsheets. More
specifically:

Import pivoted spreadsheet to table creates fields:
HSC
LOM
PlanNo
1234 (represents a maintenance action, data is current frequency)
1654
V001

Renamed fields become:
HSC
LOM
PlanNo
C1234 (current frequency)
C1654
CV001

Inserted fields are:
P1234 (proposed frequency)
P1654
PV001

Now my table field order is:
HSC
LOM
PlanNo
C1234
C1654
CV001
P1234
P1654
PV001


What I need is:
HSC
LOM
PlanNo
C1234 (current for 1234)
P1234 (proposed for 1234)
C1654
P1654
CV001
PV001


Only HSC, LOM and PlanNo are static fieldnames, as the imported spreadsheets
will have different P and C field names. There might be only 6 additional
fields or there might be 30, depending on the data being brought in.

So... how can I re-order these fields?

Thanks for any and all help!

Susan

 

[Guest]

It does not matter what order the fields are in the table.

But if you open the table in design view you can drag the fields up or down.
Just highlight the field by clicking on the left column and then drag.

 

[SusanV]

Hi Karl,
Normally I would totally agree with you, but in this case it does matter, as
users will be simply pulling data in and outputting it via full automation
in VBA, with no interaction of tables queries or anything else. However, I
got it done using tabledef.fields inserting to temp table records, then
order by then getstring then insert into newtable. Populate the data and I'm
done...

Whew!

;-D

Susan