Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a field (a string) that looks like this: '0000000840509001' when printed on my report. I want it to look like this: '84-05-090.01'. Is there any way to do this at the report design level?

Basically, I want to strip leading zeroes and put the remaining string into a ##-##-###.## format.

share|improve this question
    
I'm assuming the data is stored as text. You'll need some VB code. AFAIK, you can't do this with the GUI. –  surfasb Jul 29 '11 at 17:12

1 Answer 1

up vote 0 down vote accepted

sadly there is no single-method for stripping leading zeros.... but here's a little trick I learned...

format(replace(ltrim(replace([your-field-name],"0"," "))," ","0"),##-##-###.##)

broken down...

format(
  replace(
    ltrim(
      replace(
        [your-field-name],
        "0",
        " "
      )
    ),
    " ",
    "0"
  ),
  "##-##-###.##"
)

The method to the madness is to replace all zeros with a space-character then "trim" it on the left-side only. (we don't want the ending-zeros to go away) then replace the space characters back with zeros... and finally format it the way you want.

Alternatively... rather than trying to trim the digits... you can also convert it to an actual integer using cint() and then format it like so:

format(cint([your-field-name]),"##-##-###.##")

Either method may not work the way you want... simply because there is no error checking if the provided input is valid. i.e. if you have 10 (or more)significant digits instead of 9... it would silently truncate the leading digits... also using cInt won't accept "characters" like "0000000ABCDEFGHI" or whatever...

You may want to actually create a function in vb-script to do your formatting properly... and then use it in your report. But this is a much more complex subject... so I am not going to touch it here.

share|improve this answer
    
Thanks for your help, Wiz! I'll tinker with both solutions that you provided and see what happens. Again, I appreciate your help. –  Kevin Jul 29 '11 at 17:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.