Integer to Fixed Width Text 2

[Milla]

I have a table that has an Integer Value in a Column. We'll call this "sequence number", or "Seq".

I need to reformat the entire column to a 7-character, right-justified, space-padded text field:

In other words:

Have              Need
=====             =======
1                 0000001
2                 0000002
3                 0000003
4                 0000004
...
245               0000245
246               0000246
...
1034              0001034

Does anyone have a snippet of simple (I'm a rookie) code that can recurse through the table and make this modification?

Thanks!

 

[Creepers]

Dim LoopTimes as integer
Dim SequenceNumber as variant
LoopTimes= Len(SequenceNumber)
Do While LoopTimes < 7
SequenceNumber = &quot;0&quot; & SequenceNumber
LoopTimes = LoopTimes + 1
Loop

 

[mpastore]

Use the format() function. Example:

?format(7,&quot;0000000&quot

Yields 0000007

For an update you could do something like:

update [mytb] set [mytextfield] = format([mynumberfield],&quot;0000000&quot


Mike Pastore

Hats off to (Roy) Harper

 

[Milla]

WOW

That was Fast!

I'll try it and let you know.

Thanks very much!

 

[tonyflavell]

Good one MPASTORE, I have never used this method before. I may change some code in some of my databases to this. Worth a star at least!

Tony

 

[Milla]

Worked like a charm and it was SIMPLE.

Thanks, Mike

(Agree with Tony, definitely worth a star.)