Format field in Make table query

[Limbo_111]

DB Setup:
Table1: I have a table (Vendor) that has 2 fields (# & Name) with # being an AutoNumber. So only Name is being input via a form. I have formatted the autonumber field as 000;(000).
Table2: A table that is populated via form with invoice info etc and vendor number is added through a drop down combo box (which also has the above format on it)
Table3: Similar to table2, with slightly diff info but still vendor #

Query1: Is a make table that consolidates table 1 & 2 via union on like fields (ie vendor #) This make table also has the format from above in its properties field, although when i open the table it makes (Table4) the vendor field is not formatted as i need it. So 3 appears as 3 not 003.

Query2: takes table4 adds some extra info and exports file (as txt or xls)using outputTo & TransferText macro so that it can be loaded into a Hyperion Essbase system

My problem is that although the field value is formatted as 003 in appearance, when i take it to excel it changes back to 3 when i need it to stay as 003. I would like the make table query to also format the tables field as 000. Is the problem with the autonumber in the orig table or is it simply excel being stubborn when i take it there. If i changed the vendor field to text string in the make table would i still be able to link it back to the orig vendor table to get the names etc (ie number field linked to text field??)
Thanks in advance

 

[neileg]

From what you say it would seem that table 2 & 3 should be reduced to one table so you don't need the union. I guess you'll have redundant fields if the two tables are identical, but the fact that you are unioning them suggests they should be combined.

Now to formatting. Since the user shouldn't be seeing the data in a table or query, I don't apply formatting except when it is essential, like in a form or in your case an export. You need to understand that formatting a field in a table or query only changes the display, not the underlying data. When you run the export, you are exporting the data as it is stored. To achieve what you want, it is probably easiest to create calculated fields in the query and use the format function Format(). This way you have changed the data in the query and not just the display.

Finally, if Excel is seeing the exported field as a number, it will apply its own default formatting. You either need to apply the formatting in Excel, or export the field as text.