MS ACCESS - I have numbers stored as TX and so there's no leading '0' aka .9 is not 0.9

Posted on 2014-07-10
Last Modified: 2014-07-10
How do I format this as a number ?

.9 to 0.9 in the MS Access
Question by:ghettocounselor
    LVL 47

    Assisted Solution

    by:Dale Fye (Access MVP)
    use the format( ) function, something like:

    Format([yourFieldName], "0.0")
    LVL 44

    Accepted Solution


    Open in new window

    Select CDbl(fieldname) From mytable

    Open in new window

    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)
    unfortunately, the format function actually converts numeric values to a string, so, depending on where you want to use this, you might simply want to set the Format property of the control on the form or report where you are displaying the value.
    LVL 44

    Expert Comment

    Then apply the Format() function to the numeric value.

    Author Closing Comment

    In the end I'm concatenating a handful of things together from two different systems one with 0.9 and one with .9 to then compare the concatenated strings searching for missing items so I needed the number to match.

    both the of the selected solutions do what they are intended to do, i ended up using the CDbl function because it only changed the .9 to 0.9 and not 1 to 1.0 like the Format function, which although not specified initially was something I needed.

    Thanks for the help!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

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

    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Familiarize people with the process of utilizing SQL Server functions 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 Microsoft Ac…

    758 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

    12 Experts available now in Live!

    Get 1:1 Help Now