import from excel to access without losing format

  • Thread starter Thread starter Sandy
  • Start date Start date
S

Sandy

I have a field that has leading 0's, i.e. 00001. When the data is in Excel
it is 00001. When it goes to Access it drops the 0's. What do I need to do
so that the leading 0's are not dropped in the process?
 
Sandy

It sounds like you are letting Access decide what datatype that field is.
Access looks at several of the first rows and sees numbers, and makes the
field numeric. Leading zeros mean nothing in a number, so Access drops
them.

Another approach would be to link to the data in Excel from within Access,
then use a query to append from the Excel data into an Access table in which
you have defined that field as "text".

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Hi Sandy,

Don't you just hate it when Access does that? I sure do... it's nasty when it does that to phone numbers that are in three columns... 800 555 0025 becomes 800 555 25. Not to mention ruining invoice numbers that are commonly padded with leading 0'; then they don't match the contents of another table you are trying to join with them.

I haven't found an easy way to get Access to 'do the right thing' excepting to create a table that is all text fields with the same headers as the Excel sheet, import to that table, then append the data from that table to the target. As long as you are importing to a new table or linking - it's a problem.

On the other hand, if your leading zeros column is fixed length (always five digits or whatever) then you can reapply the padding this way.

To display a padded result Expr1:Right("00000" & [myleadingzerofield], 5)
or
you can update the column by setting the myleadingzerofield = Right("00000" & [myleadingzerofield], 5)

What this does - takes your field contents, using your example, it would be 1 - and inserts five zero's in front of it, so that it looks like 000001. However, that is too many digits, we only want 5 so what we do is we take our 5 digits from the right hand edge, giving us 00001. If the field value was 25, then we would have 00025, and so forth.

Hope this helps...
Gordon
 
Back
Top