Re: Convert Number to Text and keep leading zeroes

[Jeff Boyce]

A potential problem is that if you have, say, "001234567" as a number, you
actually have "1234567", and the zeros are not there to convert to text.

One possible approach would be to add a new field to the table, of type
text, then use a query to append the number data to the text field. You'd
need to use formatting to show "1234567" as "001234567", and you might need
to use CStr() to convert that to a text data type before appending.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Spencer MVP]

You can use the format function to convert the number to text with the leading
zeroes.

Format([SomeField],"000000000")

If you change the field type to text, all the numbers will be saved. Once you
have done that you can use an update query and update the field to the format.

Field: SSN
Update to: Format([TableName].[SSN],"000000000")

If you add a new field to hold the text version
UPDATE SomeTable
SET NewSSN = Format([SomeTable].[SSN],"000000000")

If you want to add spaces or dashes as separators, you would just change the
format definition. "000 00 0000" or "000-00-0000"

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County