5-DIGIT FORMAT IN TABLE

[William]

I have a table that imported a field from an Excel file with 5-digit numbers.
Some of these numbers began with 0 in the Excel file, but in the Access
import show as 4-digit numbers, having dropped the 0 at the beginning.

How can I get that 0 to show in Access?

 

[Wayne-I-M]

Some of these numbers began with 0

Number can't begin with 0, you could import them into a text field

 

[William]

I converted the numbers to text in design. The problem then is that Access
scatters all the 4-digit numbers througout the field like this:

11112
1113
11114
21111
21112
21113
2114
21115

Is there a way to avoid having to hunt for all these 4-digit numbers and add
a zero at the front after converting the field to text?

 

[Amanda]

If you only want to 'show' the numbers, you could set the format of the
colomn to be '00000'. Access will store the 4 digit number, but will show 5
with the format.

 

[Dad3353]

William...

I suspect that the numbers in the Excel file are exactly that: numbers. The
leading '0' that you see in Excel is simply cell formatting such as '0####',
which forces the display.
Is it possible to convert the number cell in the Excel source file to be
text (select the column in question, change the format to 'Text'...). The
leading 0's will have to be present in the Excel cells for real, not just
formatted in. Access will correctly import such cells as text, including the
leading 0,s.

 

[Wayne-I-M]

Hi Amanda

Not sure that what he is looking for - all you do with the format 0000 is
provide a format (not a number)

so
00123 + 1 = 124 ( not 00124)

 

[Wayne-I-M]

You don't need to - just import them into a text field (as that's what they
are).

You can use an update query to update the text to add the leading zero if
you want (but what's the point)
Create a new Text field in your table (called it NewTextField)

UPDATE TableName SET TableName.NewTextField = "0" & [TableName]![NewTextField]
WHERE ((Len([TableName]![NumberField])=4));

 

[Wayne-I-M]

ooops - shoulod be

UPDATE TableName SET TableName.NewTextField =
IIf(Len([TableName]![NumberField])=4,"0" &
[TableName]![NumberField],[TableName]![NumberField]);


or you will loose your original 5 digits number/texts


--
Wayne
Manchester, England.

You don't need to - just import them into a text field (as that's what they
are).

You can use an update query to update the text to add the leading zero if
you want (but what's the point)
Create a new Text field in your table (called it NewTextField)

UPDATE TableName SET TableName.NewTextField = "0" & [TableName]![NewTextField]
WHERE ((Len([TableName]![NumberField])=4));


--
Wayne
Manchester, England.

I converted the numbers to text in design. The problem then is that Access
scatters all the 4-digit numbers througout the field like this:

11112
1113
11114
21111
21112
21113
2114
21115

Is there a way to avoid having to hunt for all these 4-digit numbers and add
a zero at the front after converting the field to text?

 

[Douglas J. Steele]

Or

UPDATE TableName
SET TableName.NewTextField = Right("00000" & [TableName]![NumberField]), 5)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

ooops - shoulod be

UPDATE TableName SET TableName.NewTextField =
IIf(Len([TableName]![NumberField])=4,"0" &
[TableName]![NumberField],[TableName]![NumberField]);


or you will loose your original 5 digits number/texts


--
Wayne
Manchester, England.

You don't need to - just import them into a text field (as that's what
they
are).

You can use an update query to update the text to add the leading zero if
you want (but what's the point)
Create a new Text field in your table (called it NewTextField)

UPDATE TableName SET TableName.NewTextField = "0" &
[TableName]![NewTextField]
WHERE ((Len([TableName]![NumberField])=4));


--
Wayne
Manchester, England.

I converted the numbers to text in design. The problem then is that
Access
scatters all the 4-digit numbers througout the field like this:

11112
1113
11114
21111
21112
21113
2114
21115

Is there a way to avoid having to hunt for all these 4-digit numbers
and add
a zero at the front after converting the field to text?
--
William


:

Some of these numbers began with 0

Number can't begin with 0, you could import them into a text field


--
Wayne
Manchester, England.



:

I have a table that imported a field from an Excel file with
5-digit numbers.
Some of these numbers began with 0 in the Excel file, but in the
Access
import show as 4-digit numbers, having dropped the 0 at the
beginning.

How can I get that 0 to show in Access?

 

[William]

Thanks to all. The simplest solution was to just format the access field
00000.