469,266 Members | 1,679 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,266 developers. It's quick & easy.

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 19676
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.fldNumberField =
Switch
(
Len(tblMyTable.fldNumberField)=1,"00" &
tblMyTable.fldNumberField,
Len(tblMyTable.fldNumberField)=2,"0" & [fldNumberField,
Len(tblMyTable.fldNumberField)>2,tblMyTable.fldNum berField
);

</AIRCODE>

HTH

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

Nov 13 '05 #4

"fredg" <fg******@example.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.TxtNumFld = "0" & [TxtNumFld]
WHERE ((Len([TxtNumFld])="2"));

Tom

OneDay wrote:
"fredg" <fg******@example.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 discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by david | last post: by
5 posts views Thread by samik_tanik | last post: by
1 post views Thread by mmmgood1 | last post: by
6 posts views Thread by Clint Stowers | last post: by
1 post views Thread by LFM | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.