Well this is probably simple and I am overlooking how to do it so I hope someone can please assist me.

I have a studentid field that has a number in it. The leading zero is missing on all the numbers in this field because the excel file was formatted incorrectly and it lost the leading zero for every student id.

What I need to do is insert a 0 (zero) in front of each number in the field. I can't figure it out all I manage to do is replace it. :-(

Any help would be greatly appreciated.

Edited October 4, 200619 yr by Guest

i tried this in a script and it worked for me:

Insert Calculated Result [select; yourfield; "0" & (GetAsText ( yourfield ))]

i made a button and attached the script to it.

each time i click it it adds a preceeding zero.

you could make it loop through your records to replace all the fields. there may be a better way.

the field is text however.

You should always try to use Set Field instead of Insert Calculated Result. These script steps perform a lot of the same actions but Set Field doesn't require the target field to be on the current layout or any layout. It's better not to marry a script to a layout if you can help it.

Also, I would recommend the following formula since it will handle placing multiple leading zeros if necessary. In the following example, I want 2 leading zeros in from of single digit numbers, 1 leading zero in front of double digit numbers and no leading zeros in front of three digit numbers (i.e. 005, 034, 123).

Set Field [MYTABLE::myfield; Right("00" & MYTABLE::myfield; 3)]

Edited October 4, 200619 yr by Guest

i just switched out a use of InsertCalculatedResult to SetField and it certainly does seem nicer.

Thank you both. I tried both way and the Setfield was a lot easier because I could not figure out how to end my script when I got to the last record. But with the setfield it did not keep adding zeros :-)

Thank you so very much!! :

Sounds like you are using a looping script to run the Set Field. Typically, the way to exit a looping script is to check the option on the Go to Record/Request/Page step to "exit after last". Or, you could use the formula inside a Replace Field Contents command and it will run the formula on every record in the found set.