Need to Add Zeros to Field (1 Viewer)

[fhs]

I’ve got a six-character text field with values like: 354, 7237, 10438, all values under six-characters long. I need to run an update query to place sufficient “zeros” in front of each value to make it a six-character value. There are 8398 records to change! Got an idea? Please lemme know. Thanks.

 

[irishjoe]

How about this, I know it ain't elegant but should do the job.

Public Function xxx(intValue As Integer) As String

If Len(intValue) = 1 Then
xxx = intValue + "00000"
ElseIf Len(intValue) = 2 Then
xxx = intValue + "0000"
ElseIf Len(intValue) = 3 Then
xxx = intValue + "000"
ElseIf Len(intValue) = 4 Then
xxx = intValue + "00"
ElseIf Len(intValue) = 5 Then
xxx = intValue + "0"
ElseIf Len(intValue) = 6 Then
xxx = intValue
End If

End Function

 

[fhs]

Thanks for the function, however, when I place xxx([fieldname]) in the Update To: field of an Update query, I get error message "Undefined function "xxx" in expression." What am I doing wrong?

 

[irishjoe]

where did u put the function?
If you put it in a module and make sure it is public, not private, it should be accessible by the query

 

[Smart]

Adding zeroes

If you enter the following line of code in the query's Field name

Expr1: IIf(Len([a])=1,"00000"+[a],IIf(Len([a])=2,"0000"+[a],IIf(Len([a])=3,"000"+[a],IIf(Len([a])=4,"00"+[a],IIf(Len([a])=5,"0"+[a],[a])))))

if the values in a are
1
12
123
1234
12345
123456

After applying the above code they will appear as

000001
000012
000123
001234
012345
123456

hope this helps