Need to Add Zeros to Field

fhs

Registered User.
Local time
Today, 18:21
Joined
Aug 8, 2001
Messages
68
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.
 
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
 
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?
 
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
 
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
 

Users who are viewing this thread

Back
Top Bottom