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

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

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris BottomleySoftware Quality Lead EngineerCommented:
Hello doyle007,

Try something like:

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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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).
doyle007Author Commented:
Appologies I have been away. This answer is perfect..
Chris BottomleySoftware Quality Lead EngineerCommented:
Your welcome, no worries and thanks for the grade

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.