Pad field in query

Guest · Mar 4, 2004

I'm working with a database over which I have no design control. I'm trying to take the data from this database and transform it into a format that another database can use. However, one of the columns in the db I'm working with was defined as numeric, but the system using this value sees it as a text field of length six (padding to the left with zeroes)

Is there any way within a query that I can take the value and pad it with zeroes so that it's length is 6? So, for instanc
1 becomes 00000
78 becomes 00007
363 becomes 00036
etc

Again, I'm forced to link to my source table, so I can't change the design of that source column.

John Viescas · Mar 4, 2004

Format([MyNumber], "000000")

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

MDW said:
I'm working with a database over which I have no design control. I'm

trying to take the data from this database and transform it into a format
that another database can use. However, one of the columns in the db I'm
working with was defined as numeric, but the system using this value sees it
as a text field of length six (padding to the left with zeroes).

Is there any way within a query that I can take the value and pad it with

zeroes so that it's length is 6? So, for instance

1 becomes 000001
78 becomes 000078
363 becomes 000363
etc.

Again, I'm forced to link to my source table, so I can't change the design

of that source column.

Michel Walsh · Mar 4, 2004

Hi,

That would become a ALPHA value, not a numerical one. It is generally
better left for PRESENTATION on a form than for storage in a table, but
anyhow, using FORMAT can help:

Format( MyNumber, String(6, "0") )

Hoping it may help,
Vanderghast, Access MVP