How can I specify an integer's total number of digits in VBScript?

Question

I have a "text field" in SSRS in Visual Studio 2008 that I would like to convert to a number in order for the export to Excel to work. Simply changing the properties to Number does not work. So I've decided to try to write some code in the Value field for it to work.

I have an invoice number that must always have seven digits, e.g. 1938576, 0000001. Using CInt(Fields!Invoice.Value) does not work as it does not keep any leading zeroes. How can I convert this field to a numerical value (integer for this one, but I have a couple other fields that it must work with doubles) while also determining exactly how many digits must be shown? Something like CInt(Fields!Invoice.Value, 7) would be great, but I don't think that parameter is part of the function.

I think the only way to have leading zeroes is to format it as a string. — Tom Pietrosanti, Jul 27 '12 at 18:27
An integer does not have leading zeroes, an integer is a number. Leading zeroes are not a property of an integer, but a property of a text representation of an integer, in other words, a string. Why do you need to export it as a number? — , Jul 27 '12 at 18:28
It needs to be a number so that it can interact with the Excel workspace. — Bradley Oesch, Jul 27 '12 at 20:08
in BIDS could you not just format it as 0000000 rather than the usual #,###,##0.00 ? — whytheq, Jul 29 '12 at 12:18

score 1 · Accepted Answer · answered Jul 27 '12 at 18:30

VBScript doesn't have any number formatting functions that will work for this. You have to write your own.

Function PadNumber(x, digit_count)
    If Len(x) < digit_count Then
        PadNumber = String(digit_count - Len(x), "0") & x
    Else
        PadNumber = x
    End If
End Function

How can I specify an integer's total number of digits in VBScript?

1 Answers1