string length in Access table 3

[ppenn]

I have a table with thousands of records, one of the fields contains figures of varying length (none more than 10 digits) Is it possible for code to search through each record, establish how many digits it has and then add zeros at the beginning to make the original number up to 10 digits. IF this seems a bit strange it is necessary in order to compare data with other data imported from an AS400 system. This task is normally performed manually and is quite time consuming.
Many, many thanks to anyone who can help or point me in the correct direction.

 

[SgtJarrow]

sure.....but not default...

First back up your database!!!

Paste the following into a new module:

'***********Start Code***************
Public Function PadText(strText As String, intWidth As Integer, _
Optional strPad As String = "0", Optional strDirection As String = "Left&quot As String

If Len(strText) > intWidth Then
PadText = Left(strText, intWidth)
Else
If strDirection = "Left" Then
PadText = Right(String(intWidth, strPad) & strText, intWidth)
ElseIf strDirection = "Right" Then
PadText = Left(strText & String(intWidth, strPad), intWidth)
Else
MsgBox ("Invalid Pad Direction&quot
Exit Function
End If
End If

End Function
'*************End Code**************

Save it as anything. Then using an update query add your table, add the field, and put:

PadText([FieldName],10)

in the update to line....run the query and you are done.

****************************
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[ppenn]

Hello Robert
Thank you very much - that worked brilliantly - you must be a genius !!!!!
Regards
Peter

 

[MichaelRed]

hmmmmmmmmmmmmmm,

It is not usually necessary to actually change dataqvalues to add the "Pad". Using the same process, you can pad the value for the purpose of the comparison only.

If the original data is numeric, the procedure will not actually change the data anyway.

If the data is string, then the data will actually be changed, but other processes within the db may be affected, such as compares to other persistient data, form and report 'formatting', data entry requirements, etc.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[DrBowes]

Much easier........

Put the columns from the table in a query and add the expression (assuming your field is Field 1)

Expr1: Right([Field1]+10000000000,10)

If you wish you can hide the old number field and make this the new field in a make table query.