Convert text into number with decimals

Posted on 2012-08-17
Last Modified: 2012-08-17
I am importing a large group of Excel files that includes two columns of numbers that are formatted as text. I import them, as is, into a temp table and now am trying to append them to the final table while changing the data type from text to numeric.  The problem I am having is that when I change the data type the numbers are being truncated to integers.

I have tried Value, CInt, CDbl, CSng and also tried changing the final table format to General Number, Single, Double, Fixed Format, Standard Format, General Number Format with Decimal set to a specific number and to Auto. Every time the number loses anything to the right of the decimal point.

Any suggestions?
Question by:JazCat
    LVL 65

    Accepted Solution

    CDbl() should work, even with leading and trailing spaces.

    ?CDbl("  123.456  ")

    Perhaps you have to test to see if the values are numeric, otherwise if any values are not it will cause the expression to fail.

    ?iif(IsNumeric("  123.456"), CDbl("  123.456"), "X")
    LVL 47

    Assisted Solution

    by:Dale Fye (Access MVP)
    Make sure the data type of the field you are trying to push them to is Double.  Then make sure that the display format is set to General or Fixed with a specific number of decimal places.

    Author Closing Comment

    Thanks, both of these helped. I thought I had used the Double, Fixed, 1 decimal place format but I guess not cause that seemed to work. I also added the IsNumeric check just in case.  Interesting, the Double, General, 1 decimal place did not in all cases add a number after the decimal and also sometimes added two.

    Thanks both of you for your quick response.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    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…
    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
    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.

    732 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now