How can I format a text field in Microsoft Access Report Designer?

Question

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.

Answer 1

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.