Turning ID into a defined format. 1

[griswom]

Hi all..

This is a first time post. Here is my problem. I have a bunch of preexisting records with a field called "ID" The data in the field has varying lengths such as:
1
12
123
1234

etc.
I would like to write a function that looks at this field and then transforms it to fit the format
00000000"-000". I have an idea that I could accomplish this using the len function and then looping it and adding a zero each time until len=8, and then concatenate the trailing piece, but I am not good enough w/ VBA to accomplish this. Any advice would be appreciated.

Thanks,

Griz

 

[SgtJarrow]

Will the new numbers begin with the existing ones?? I.E.

1 10000000-000
12 12000000-000
123 12300000-000
1234 12340000-000

I have some pre-existing code that will do just this for you.... Programming isn't a profession of choice.
It's a profession of calling...
"Hey Programmer, your application broke again!"

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer

 

[griswom]

Mr. Johnson,

Thank you for the quick resonse. Actually I would want to add leading zeroes until it fits the format. i.e.
123 becomes 00000123-000. Maybe it wouldn't be too hard to modify your preexisting code?

Thank you.

Griz

 

[SgtJarrow]

Nope.....my code will handle the pad to either side....

Just place this code in a module and call it as necessary...

Your call would look like

PadText("ID", 8)

The Optional statments are already set the way you want to have this work...and then you can simply add the trailing "-000" as necessary. Let me know if you need anything else.


'***************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 = strText
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 Programming isn't a profession of choice.
It's a profession of calling...
"Hey Programmer, your application broke again!"

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer

 

[griswom]

Mr. Johnson,

Thank you so much for your assistance with this. Now I am going to reveal my ignorance. I copied your code into a module called NewID. I am now having trouble figuring out how to call the function to run this code on my table to update the field Old ID. Thank you for your help.

Griz

 

[SgtJarrow]

OK....here's what I did....I created a small table with some sample data similar to that which you provided. I then created an update query that contained only the ID field from this table. In the Update To portion of the query, I PLaced the following code:

PadText([Table1]![id],8) & "-000"

And I got the results you were asking for...

1 became 00000001-000
12 became 00000012-000
and so on.....

You can follow the same steps, just be sure to change the Table1 to your table name and check the id name portion too....let me know how it goes. Programming isn't a profession of choice.
It's a profession of calling...
"Hey Programmer, your application broke again!"

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer

 

[griswom]

Mr. Johnson,

You have no idea how much better you just made my life. I did exactly as you said and it works beautifully. You just saved me hours of work every week! Thank you both for your patience and assistance.

Griz