Solved

How deal with scientific notation values

Posted on 2013-06-10
8
294 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 45

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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 45

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 45

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 500 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

830 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