Field formatting.

Question

Hello

I am working with data from MS Excel spreadsheets which contain a field with 13 digits, which can include leading zeros. A quirk with MS Excel is that it tends to automatically display long numbers with scientific notation (i.e. 1.21E+12) or truncate the leading zeros, for example

Actual (how it should be displayed):

A) 0000000001234

B) 1234567890123

How it appears on spreadsheet:

A) 1234

B) 1.23E+12

I know this can be treated within Excel, but since I work with dozens of spreadsheets, this becomes time-consuming - and I need to perform other data transforms within Access anyway - so was wondering if there was a way Access can display the fields as it should appear when you import data from the spreadsheet into a data sheet in Access? 

I will be extremely grateful for any advice received.

PS: Apologies in advance if this is something obvious- I am still an Access newbie!

Immz

Answer 1

You could use Format to display it properly

Format(YourNumber, "0000000000000")

Format(1234, "0000000000000") -> 0000000001234

The issue with your input is that 1.23E+12 does not equal 1234567890123!  You'll have to test and see if Access receives the actual proper value or the rounded 1.23E+12 with which there is little you can do.

Depending on what these numbers represent and what you need to do with them, in certain cases it can become easier to use a text field to work with them.

---

Daniel Pineault, 2010-2017 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 2

I'm not sure how you are importing the data from Excel to Access, but there are ways to specify that the data be imported as "text" as opposed to numeric values, so you don't lose formatting. Perhaps you could elaborate on how the data is being imported.

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 3

Hi

Probably not the best way, but I'm simply pasting the data into an Access datasheet. I predefined the format as text so it loses the scientific notation, but the leading zeros still disappear.

Immz

Answer 4

If you want to store the leading zeros as part of the value in the column in the table, then the column must be of text data type, not a number data type.  If you merely want to present the data with leading zeros then you can store the data in a column of long integer number data type and format it to show the leading zeros, as Daniel described earlier.

If the values are importing into a column of text data type, but still dropping the leading zeros, it is a simple task to add them with an UPDATE query:

UPDATE [YourTableName]
SET [YourColumnName] = FORMAT([YourColumnName],"0000000000000");

---

Ken Sheridan, Stafford, England