Complete with zeros at the left a column data. 1

[infogeslan]

Ok, now here is what I need to do. I must be able to put as many ceros as necessary to complete de number to 11 characters in a column. This is

I have now

fieldnumbers

898
36373
2335
...

I need to get

00000000898
00000036373
00000002335

How can I do this automaticaly. I have 111411 records.

Please, help will be appreciated.

 

[FoxProProgrammer]

Hi infogeslan,

This should do the trick.

Private Sub btnPad_Click()

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("tblNumConvert", dbOpenDynaset)

While Not rs.EOF
rs.Edit
rs!newfldnumbers = ConvertNum(rs!fieldnumbers)
rs.Update
rs.MoveNext
Wend
End Sub



Function ConvertNum(Num2Conv As String)

Select Case Len(Num2Conv)
Case 1
ConvertNum = "0000000000" & Num2Conv
Case 2
ConvertNum = "000000000" & Num2Conv
Case 3
ConvertNum = "00000000" & Num2Conv
Case 4
ConvertNum = "0000000" & Num2Conv
Case 5
ConvertNum = "000000" & Num2Conv
Case 6
ConvertNum = "00000" & Num2Conv
Case 7
ConvertNum = "0000" & Num2Conv
Case 8
ConvertNum = "000" & Num2Conv
Case 9
ConvertNum = "00" & Num2Conv
Case 10
ConvertNum = "0" & Num2Conv
End Select
End Function
dz
dzaccess@yahoo.com

 

[FoxProProgrammer]

I left out two statments.

Place this code after the Wend.


rs.Close
db.Close

Also, replace your table name for "tblNumConvert".

You will need to create a new field in your table called newfldnumbers. Make it text type. I also assumed that your existing field is named fieldnumbers.

Best, dz
dzaccess@yahoo.com

 

[tlbroadbent]

How about a real simple solution? Create an update query.

Update Your_Table
Set FieldNumber=format(FieldNumber,"00000000000&quot

Of course, this only works if the FiledNumber column is a text data type. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[tlbroadbent]

In addition to simple, the update query will be much faster than looping VB code. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[FoxProProgrammer]

I thought of using the Format function before I posted the Pad function, but it didn't work. Now, I got it to work with Format. The code is simpler and I would opt for that. However, if this is a one time conversion, and it sounds like it is, the difference in speed between an Update Query and looping through the records is neglible. I would guess that the difference is a matter of seconds. If this is repeated over and over again I would agree with you. There are many ways to accomplish the same end result. Thanks for your input. dz
dzaccess@yahoo.com