VBA Access The leading 0 of a number is always removed. i.e: 1.10 = 1.1

Posted on 2008-11-19
Last Modified: 2013-11-27
I have a table with a column of 12 values: 1.01 to 1.12
the column can be set to either Decimal or Double, with an Input Mask setting the field to = #.##
no matter what value I put in.

However when I retrieve the value via some VBA Code 1.10 turns to 1.1. I desperatly need it to stay as 1.10 like wise if the value = 1.20  I want it to stay at 1.20.
I could create a string however you can't sort a string if the values are numbers in a table.

Upon creating the table and inputting the values, I then run the following Code (Code Section).

Can I ask one of you experts please help me to retain the leading zero of numbers such as 1.10 and KEEP it as a number when runnning it through the code below.

Option Compare Database

Option Explicit

Public Sub testing()

Dim db As Database

Dim Rst As Recordset

Dim Sql As String

Set db = CurrentDb

Sql = "Select Numbers from table1 order by Numbers asc;"

Set Rst = db.OpenRecordset(Sql, dbOpenSnapshot)

Do Until Rst.EOF

   Debug.Print Rst.Fields(0)



End Sub

Open in new window

Question by:doyle007
    LVL 59

    Accepted Solution

    Hello doyle007,

    Try something like:

    debug.print format (Rst.Fields(0), "#,###.00")

    LVL 84
    For the record, you're referring to "trailing" zeros, not "leading", and Chris is correct - just format it after you get it out of the table. Access will never store a trailing or leading 0 with a numeric datatype, so you cannot "force" this (unless you change to a Text datatype, and then you'll have other issues to deal with).

    Author Closing Comment

    Appologies I have been away. This answer is perfect..
    LVL 59

    Expert Comment

    by:Chris Bottomley
    Your welcome, no worries and thanks for the grade


    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    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…
    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…
    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…

    729 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

    19 Experts available now in Live!

    Get 1:1 Help Now