Results 1 to 4 of 4
  1. #1
    Profector is offline Novice
    Windows XP Access 2003
    Join Date
    Oct 2010
    Location
    Kansas City
    Posts
    4

    Importiing large numbers decimal problems

    Hi all,



    I'm importing data from a csv text file and creating a new table in Access 365, then running a query on that data and exporting the results back into another csv. This process has worked fine for the longest time. Recently though, I started working with the new dataset which has larger numbers. Some of them are greater than 200,000,000.00. For some reason when I export these numbers a few of them are off by a .01. Oddly those values aren't being manipulated by query and it's only the largest values that have the problems. I assumed the problem was with the data type but I've tried importing as text and as a double but without luck. I've also by passed the query completely, just imported and then exported the data, and still had the problem. Any ideas? Thank you.

  2. #2
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Maybe this would help.

    Use decimal and specify scale and precision properties in the table.
    Precision is the total number of digits allowed in a number, including left and right of the decimal.
    Scale is the number of digits allowed to the right of the decimal.


    So 1234567.89 has a precision of 9.
    123456.789 has a precision of 9 and a scale of 3.


    A precision of 4 and scale of 2 would result in a number with a maximum value of 99.99


    Percent designations like 125.5% would require precision 4 and scale of 3 (not one) since it is actually stored as 1.255.


    Precision has a maximum value of 28, but textboxes in forms allow only a max of about 15 decimal places.

  3. #3
    Profector is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2010
    Location
    Kansas City
    Posts
    4
    Thank you That may help with some changes to my process. The first time I import a file data I create a new table, then save the routine. This way I can used saved imports. Each time I import it overwrites the table with new data. When we import data and overwrite, precision isn't an option.

    The other import option is to append the data. That might work if I setup a table with the correct data type then used a delete query to clear the table before every import.

  4. #4
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    The other import option is to append the data. That might work if I setup a table with the correct data type then used a delete query to clear the table before every import.
    That's what I would try. FWIW, it's better practice to use a permanent table with delete/append like that, than to continually delete and re-create the table.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How to Handle Very Large Numbers?
    By HumanJHawkins in forum Access
    Replies: 1
    Last Post: 02-05-2020, 11:40 AM
  2. Decimal Problems populating a combo box
    By jlindquist23 in forum Access
    Replies: 3
    Last Post: 05-10-2019, 06:07 PM
  3. Formatting Numbers in Decimal Places
    By bigmatt911 in forum Queries
    Replies: 3
    Last Post: 12-20-2013, 12:46 PM
  4. Format decimal numbers with fixed width
    By Persist in forum Programming
    Replies: 5
    Last Post: 07-14-2010, 05:43 PM
  5. Non programer needs help in decimal numbers
    By Peter O in forum Access
    Replies: 1
    Last Post: 10-07-2008, 12:29 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums