changing field formats 1

[s0ccerdude]

After importing a number field from MS Excel to Access (2002), I want to change the format in Access to text AND THEN change the format of the numbers so that they are all 4 digits.

For example: 0001, 0027, 0203
Instead of: 1, 27, 203

I've messed around with the different options in the design view of the table that contains the data (i.e. format, input mask, field size) but have had no success in changing the data to the desired format. The only way that I've been able to do this is by changing the format in Excel, and then copying the changed numbers directly into the Access table (importing does not work here, only copying).

Can anyone help?

 

[Wiszh]

Try this:

1) Import the spreadsheet into access

2) Change the data type to text

3) Run the following query three times:
Update MYTABLE set MYField = '0' & MYField
Where Len(MyField) < 4

Good Luck

 

[s0ccerdude]

Thank you very much Wiszh!
Your answer worked like a charm!