MS Access - Trying to format numbers with 12 digits? Need help :)?

Question

I have numbers that I am importing from Excel into Access that have 12 digits (ie. 47 billion but with no commas) I have tried formatting in Excel first, in Access, but nothing works... the number looks like this when I import it 4.7180130E+11. I cant get it to read 471801304895. I tried changing it to a long integer and it deletes the data. Any ideas on how to fix?

Answer 1

The way the number is being displayed (4.7180130E+11) is in fact known as Scientific Notation, sometimes also referred to as Exponential Notation. It is a way of representing very large numbers, like 4.7180130 * 10^11 (10^11 is 10 to the eleventh power).



If a number from Excel is a whole number (no decimal portion), put it into a Long (variable or field) and display it in a Text Box on a form or report using the Format function.



For example: txtNumber = Format([myfield], "############")



If you want to test it first, open the Immediate Window (aka Debug Window) with Ctrl-G

then type: ? format(4.7180130E+11,"############")



If it isn't a whole number, then use Single as the datatype and include a decimal point followed by pound signs indicating however many decimal positions you want to see.



Pound signs in the Format function are placeholders. They display either a digit or nothing.



Also Note: the maximum value a Long can hold is 2,147,483,647. If any imported numbers are larger than that they will HAVE TO go into a Single datatype. You could also use a Double but I doubt if you need that much precision.

Answer 2

Ms Access Format Number

Answer 3

I think the field may be in scientific format. Change it to general number. (it's the option under Field size) when looking at the table structure view

Answer 4

hi girld



is just a matter of formatting presentation… you can have the number in scientific notation or format with commas



if you do not want to see the scientific notation, the field size must be double with “standard” format

Answer 5

problematic problem. do a search on google. this will help!