field length

[Cordeiro82]

I got an ID field (text field) containing IDs with varying lengths. Some are 4 characters (9455) and some go all the way up to 9 characters (900034233). Is there any way to convert all IDs to a 10 character format by having the required X amount of zeros precede each ID?

Thanks,

Kevin

 

[DotNetGnat]

try

Format([MyFieldName], "0000000000")

-DNG

 

[lynchg]

The format function only works in displaying values, it doesn't change the data in the table.

I think you might have use code to update those values since they are of varying lengths.

Create a recordset with just that one field in it and loop through the records. Within that loop try something like this:

incr = 0
counter = 10 - len(rst.IDfield)
prefix = ''

while incr < counter
prefix = prefix & '0'
loop

rst.IDfield = prefix & rst.IDfield
rst.Update

I would have to check the exact syntax but hopefully you get the idea. It seems a little clumsy to me but this idea should work.

 

[Golom]

If you want to change the values in the table (as opposed to just displaying them with leading zeros), run an update statement

UPDATE myTable 

SET ID = Right("0000000000" & ID, 10)