473,890 Members | 1,262 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

leading zeros on a text field

I've got a field that has some old data with text in it, but all forward
data will be a 3 digit number. But many of the numbers are still only 2
digits. I would like to force the leading zero in the entry of the field.

For example if the number 77 is entered into the field, 077 will display.

How do I format to force the leading zero?
Nov 13 '05 #1
5 20437
On Wed, 12 Jan 2005 21:15:57 -0500, OneDay wrote:
I've got a field that has some old data with text in it, but all forward
data will be a 3 digit number. But many of the numbers are still only 2
digits. I would like to force the leading zero in the entry of the field.

For example if the number 77 is entered into the field, 077 will display.

How do I format to force the leading zero?


If the field datatype is Number, set the format property of the
control to:
000
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
Nov 13 '05 #2
I think "Format" is what you're looking for here. The "Format" property
in TextBox if you trying to display from a Form, or you can reformat
the data using an update query with the "Format" Function. Ms-Access
help will give you examples of both.

HTH
Tom


OneDay wrote:
I've got a field that has some old data with text in it, but all forward data will be a 3 digit number. But many of the numbers are still only 2 digits. I would like to force the leading zero in the entry of the field.
For example if the number 77 is entered into the field, 077 will display.
How do I format to force the leading zero?


Nov 13 '05 #3

OneDay wrote:
I've got a field that has some old data with text in it, but all forward data will be a 3 digit number. But many of the numbers are still only 2 digits. I would like to force the leading zero in the entry of the field.
For example if the number 77 is entered into the field, 077 will display.
How do I format to force the leading zero?


If you mean displaying the data on a form, then something like this:

<AIRCODE>
Select Case Len(Me.txtField )
Case 1
Me.txtField = "00" & Me.txtField
Case 2
Me.txtField = "0" & Me.txtField
Case Else
' Do nothing, but always good practice to have a case Else!
End Select

If you mean that you want to update the data in your table, then:

UPDATE
tblMyTable
SET
tblMyTable.fldN umberField =
Switch
(
Len(tblMyTable. fldNumberField) =1,"00" &
tblMyTable.fldN umberField,
Len(tblMyTable. fldNumberField) =2,"0" & [fldNumberField,
Len(tblMyTable. fldNumberField) >2,tblMyTable.f ldNumberField
);

</AIRCODE>

HTH

Edward
--
The reading group's reading group:
http://www.bookgroup.org.uk

Nov 13 '05 #4

"fredg" <fg******@examp le.invalid> wrote in message
news:11******** *************** *******@40tude. net...
On Wed, 12 Jan 2005 21:15:57 -0500, OneDay wrote:
I've got a field that has some old data with text in it, but all forward
data will be a 3 digit number. But many of the numbers are still only 2
digits. I would like to force the leading zero in the entry of the field.
For example if the number 77 is entered into the field, 077 will display.
How do I format to force the leading zero?


If the field datatype is Number, set the format property of the
control to:
000


Due to a relationship w/ an external sql table, the field in the table has
to be a text format.

But, because the SQL database has the leading zeros, in order to get the
records to match up, the table has to have the leading zeros too.

Nov 13 '05 #5
OneDay,

You can run an update query Like:

UPDATE OneDay SET OneDay.TxtNumFl d = "0" & [TxtNumFld]
WHERE ((Len([TxtNumFld])="2"));

Tom

OneDay wrote:
"fredg" <fg******@examp le.invalid> wrote in message
news:11******** *************** *******@40tude. net...
On Wed, 12 Jan 2005 21:15:57 -0500, OneDay wrote:
I've got a field that has some old data with text in it, but all forward data will be a 3 digit number. But many of the numbers are still only 2 digits. I would like to force the leading zero in the entry of
the
field.
For example if the number 77 is entered into the field, 077 will display.
How do I format to force the leading zero?
If the field datatype is Number, set the format property of the
control to:
000


Due to a relationship w/ an external sql table, the field in the

table has to be a text format.

But, because the SQL database has the leading zeros, in order to get the records to match up, the table has to have the leading zeros too.


Nov 13 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
13792
by: david | last post by:
Hi, I have an application as follows: MySQL database Back-Eend linked to MS Access Front-End and ASP Web Application. I require users to enter Serial Numbers such as: 0105123567 (10 digits), the first 4 being the month and year (mmyy)
5
11524
by: samik_tanik | last post by:
I need to export a datagrid to Excel. I could did this. But, also need to keep the leading zeros in the data. How can I acheive this? Any help would be appreciated. -- Thanking you in anticipation, Regards,
1
4562
by: mmmgood1 | last post by:
Help, I'm linking an excel spreadsheet in access and I have datafields with leading zeros (01021). When the file is linked in access, I get a #num in the field with the leading zeros. The zeros are needed. I've formated the field to be a text field, general number, and number and still I get the #num! error, or the leading zero is dropped. Can someone help before I pull more of my hair. Thanks a bunch.
1
12794
by: Joshua Ammann | last post by:
Hello, I'm trying to export a query containing contact information, including a field. Some zip codes have one or two leading zeros, for example, San Juan, PR (00927) and Springfield, MA (01104). When I export to a comma separated value (.csv) file, the zip code for San Juan becomes "927" and for Springfield becomes "1104". How can I prevent the leading zero(s) from being trimmed in the exported .csv file? Because the table contains...
6
5313
by: Clint Stowers | last post by:
Using A2k Exporting a query to a CSV file. The problem is any text fields (i.e. 000345) lose any leading zeros. Exporting to an excel file this problem does not exist. Tried to create a SpecificationName via the Export Wizard without success. Obviously doing something wrong.
1
2637
by: Andrew | last post by:
I run a fixed-width export based on a query, and the query looks like this... Clients.SSN, Clients.ClientID, ServiceEvents.SeviceDate, CStr(Format(,"0000")) AS Servs, "28" AS SETTING, FROM Clients INNER JOIN ServiceEvents ON Clients.AutoID=ServiceEvents.AutoID WHERE ServiceEvents.Sub=False; Now, this looks fine when I few the query, and the "Unit" field is 4
3
2297
by: travellinman | last post by:
Hi, I'm trying to combine the text in two original fields and paste them into another 3rd field on a form, but the problem is that there are leading zeros in both original fields, which access does not copy into the 3rd field. For example, if field 1 = 07 and field 2 = 0001, i'd like the 3rd field to be 070001, but instead access returns 71 in the 3rd field. Adding to the complexity a bit is that field 2 is an autonumber field. so when i...
1
27895
by: LFM | last post by:
I have a database setup that uses ODBC connections into SQL for read only views to compare information from multiple sql databases in our company. Most of the tables use the Employee ID as the primary key. The problem is, one database formats the EmpID with leading zeros so all EmpID's are 5 characters long. (ie, 00123, 01523) Another database formats the EmpID as just the number of characters as the number is (ie, 123, 1523). When you...
0
4115
by: Monty | last post by:
Hi All, I am having a problem with leading zeros being stripped from fields in a CSV file when I bring them in using Jet/OleDB. In VB.Net/VS 2008, I am accessing a CSV file like so: sSQL = "SELECT * FROM " sConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _ Microsoft.VisualBasic.FileIO.FileSystem.GetParentPath(msFile) & _
0
9978
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9820
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10814
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8016
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7169
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
6041
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4676
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4270
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3276
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.