Solved

How deal with scientific notation values

Posted on 2013-06-10
8
312 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

631 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