Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How deal with scientific notation values

Posted on 2013-06-10
8
Medium Priority
?
328 Views
Last Modified: 2013-06-11
I am importing an Excel spreadsheet into a table.  One of the columns in the Excel file looks like: (example: 3.91E+11)

3.91E+11   XJ-500 DR JIM OLD US STAMP SCOTT 148 CATALOG VALUE $35
4.00E+11   XJ-501 DR JIM OLD US STAMP SCOTT 159 CATALOG VALUE $23
3.91E+11   XJ-502 DR JIM OLD US STAMP SCOTT 186 CATALOG VALUE $30
4.00E+11   XJ-503 DR JIM OLD US STAMP SCOTT 209 CATALOG VALUE $7
3.91E+11   XJ-506 DR JIM OLD US STAMP 5C JEFFERSON USED NO RESERVE


I hate to admit it but I don't understand what these values are.  But I do know that I can't display them this way.   They need to be a number field, I guess, but I don't know how to format the values in the table or forms.  They are an Item ID field.  To make matters worse, as you can see, some of the values are identical yet the description they relate to a not the same.

Can someone help?
0
Comment
Question by:SteveL13
  • 4
  • 3
8 Comments
 
LVL 20

Expert Comment

by:clarkscott
ID: 39234847
You must bring them into Excel as text.  The best way to do this is to export the data from excel as a TXT file (or export as csv and change the extension).   The reason is, in Access, you can you the ADVANCED button on the txt file import wizard and define each field.  

Scott C
0
 
LVL 46

Expert Comment

by:aikimark
ID: 39234882
3.91E+11

Move the decimal point eleven places to the right of where you currently see it

391000000000.

=============
You might be able to see the actual number if you increase the width of the column in the worksheet or you change the format of the number column or format the column to be text if it is attached to your database.
0
 

Author Comment

by:SteveL13
ID: 39234987
I'm more confused then before.  The format of the field in my Access table is Number, Single, Scientific.

The value in the original Excel file is like the example I mentioned in the original question. The Excel file name is "Example.xlsx"

I'm importing the data into Access with this code:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tblTempImport", Me.txtFileName, True

What do I need to do?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 46

Expert Comment

by:aikimark
ID: 39235198
If you want to see all the decimals, you will probably need to change the FORMAT property of the field.  The decimals are probably there, but the formatting of the column is preventing you from seeing them.
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 39235616
Access cannot display numbers larger than a long integer (2 gig and some).  But you can have a series of numeric characters that go beyond the long integer value.  That's why the "e" numbers are coming up.  If you want to see the actual numeric characters for this number (example: 999999999999) it will not be handled as a number.  Therefore, you must bring it in as text.  One of my clients deals with candy barcodes.  Quess what?  You cannot bring these values in as a value.

Your number:  391,000,000,000 is 391 billion.... way over the long integer range.


Scott C
0
 
LVL 46

Expert Comment

by:aikimark
ID: 39235657
You can change the Access column to a currency data type, which, although scaled, has enough capacity to hold a value much larger than long integer.
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 39236991
Here, create a table with a field called "money" and make it currency.  Enter this number, 390000000000.  It displays as money.   Now, create a query that divides that field value (number) by 2.  Guess what..... scientific notation result.

I guess it's all on how you intend to use it.  Again, if it's not really a numberic value you intend to use, make it a text field.  Otherwise, you will be jumping through hoops dealing with this field for the rest of the applications life.

Scott C
0
 
LVL 20

Accepted Solution

by:
clarkscott earned 2000 total points
ID: 39237019
The numberic type "decimal" exists, and it stores values as string (like currency).  This is useful for when you pull data down from db files or other "mainframe" tables.  These values can't be used for anything (math and such) for the same reasons as stated in previous comments.  I don't believe there is much "code support" for the decimal type and is not discussed much in the help.

My opinion.... make it a text field.

My 2 cents... you can give me change if you want.  :-)

Scott C
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question