Solved

How deal with scientific notation values

Posted on 2013-06-10
8
284 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

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