Link to home
Start Free TrialLog in
Avatar of doyle007
doyle007

asked on

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)
    Rst.MoveNext
Loop
 
End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Chris Bottomley
Chris Bottomley
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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).
Avatar of doyle007
doyle007

ASKER

Appologies I have been away. This answer is perfect..
Your welcome, no worries and thanks for the grade

Chris