Conditionally inserting lead zeros

[fhshoes]

I've searched through the forums, but can't find an answer specifically to what the results I'm trying to return.

The data in each column I'm returning is of varying lengths, i.e.:

123
1234
12345

I need to return results that conform to a set character length; let's say 10 characters total, i.e.:

0000000123
0000001234
0000012345

I've tried replicatestring, but that returns a set amount of whichever value you insert into the formlua, while I need it to conform to the result.

Any help would be GREATLY appreciated.

Thanks

[dglienna]

Try Format()

msgbox (Format(rst.num,"#########")

000000123

[fhshoes]

You'll have to excuse me dglienna, I'm a bit of a noob. Would I be formatting the specific field, or creating a formula using the field to drop in?

If a formula, would it be:

format({dp_correction.check_no}, "##########")

[JaganEllis]

right(replicatestring("0", 10) & totext({table.field}, 0, ""), 10)

[fhshoes]

That worked Jagan, thanks so much!

If I may, can I trouble you for one last issue?

One of the fields returns a dollar amount. Using the formula you provided, I came up with the following:

right(replicatestring("0", 10) & totext({dp_correction.amt},2,""), 10)

I understand that the '2' in the formula allows for two places after the decimal, but I need to completely remove the decimal from the result.

Is this possible?

Thanks again.

[Wenin]

right(replace(replicatestring("0", 10) & totext({dp_correction.amt},2,""),".",""), 10)

Try that?

[fhshoes]

Wenin, that worked perfectly! I can't thank you enough.

Don't know if I can ever return the favor, but let me know if I can.

Thanks again.

[Wenin]

I'm just happy to be able to give back to this forum that I've gotten plenty of assistance from in the past. =)