Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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

Posted on 2008-11-19
Medium Priority
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
  • 2
LVL 59

Accepted Solution

Chris Bottomley earned 2000 total points
ID: 23001558
Hello doyle007,

Try something like:

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

LVL 85
ID: 23002831
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

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

Expert Comment

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


Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

580 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