Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How deal with scientific notation values

Posted on 2013-06-10
8
Medium Priority
?
321 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

715 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