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.
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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).
ASKER
Appologies I have been away. This answer is perfect..
Your welcome, no worries and thanks for the grade
Chris
Chris