Adding leading zero's to numbers in a field (1 Viewer)

[IannWard]

Hi All

Is there an easy way to add leading zeros to existing numbers in a field?

Example:00001562 or 00054621 (always 8 characters)

when entering the numbers manually I don't have a problem but if I import from a spread sheet, access removes the leading zeros. I have tried setting the cells to text in excel but it keeps doing it. Would be great if I could just update the field in access to put the zeros back in.

Thanks In Advance

Iann

 

[cyanidem]

Use Format() function, either while importing data or update your field after import:
Format([MyField],"00000000")

 

[CJ_London]

a number is a number and is specified as the data type, format is format and is a property of the data. format properties are not exported/imported.

A number can be formatted to look like text - i.e. include preceding spaces, include a currency symbol, use brackets/colours for negatives etc., also dates (which are a bit special), - but this does not make it text - the underlying data remains a number so you can apply maths formula to it.

You can convert a number to text using the Format function in Access or the Text function in Excel.

As an aside, note that using text fields for criteria, filtering and sorting is slower than using number fields - though probably only really measurable on datasets of 100k records or more and noticeable on much larger datasets.

I try to avoid importing from a .xlsx/.xls because Excel makes intelligent guesses as to the underlying data type, which the user has no real control over and how to format it, which the user does have control. At the same time the Access import wizard makes intelligent guesses of the datatype based on the first 16 rows of data.

An alternative strategy to that suggested by cyanidem is try saving your Excel file as a .csv - then open the .csv in notepad to check the format of your numbers - if not right, you will need to use the text function to convert to text

 

[gemma-the-husky]

personally, i think if you need leading zeroes, then (presumably the record is a document or serial number) - then it isn't a "number" (ie one which you would use for calculations) - it's a numeric text string.

In such cases, I would use a text data type, not a number.

it still won't solve the issue of excel stripping leading zeroes. try changing the spreadsheet to treat the "numbers" as text.

or insert an extra row 2 to the spreadsheet tab , and in the "number" columns, put the word text. Then the import WILL treat the data as text, and then you can get rid of the extra row in the access table.

 

[IannWard]

You are right, its not a number. We have asset tag barcodes with 8 digit numbers (00000001 - 99999999), if I edit an entry using a form by scanning the barcode, it inserts all 8 digits, but if doing bulk entries we scan them all into a spreadsheet then import, it saves loads of time, but then I get the leading zeros missing so I end up with a field full of 4, 5, 6 or 8 digits depending on the number without the zeros.

 

[kevlray]

As we discovered recently. Excel 2007+ does a fine job of removing leading zeros when you open a .csv file. Fortunately there are ways around having Excel remove the leading zeros.

 

[IannWard]

Thanks for your response.

I'm a newbie but learning fast. How and where do I put this?
Format([MyField],"00000000")

Regards