leading zeros on a text field

[OneDay]

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?

[fredg]

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.

[Tom Warren]

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?

[teddysnips]

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

[OneDay]

"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.

[Tom Warren]

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.