Add leading zeros to Form ID # to make even length

  • 1
  • 1
  • Question
  • Updated 2 years ago
  • Answered

I am using the following formula to add leading zeros to a request # field I created on a form.

"Request"&Right("000" & ToText([Record ID#]),5)

I would like to limit the number of characters to 11 and have the leading zero's be consumed as the record id # increases.

For example:

"Request00012" should be "Request0012"

Is there a way I can modify the field or form to do this?

Photo of Traci S

Traci S

  • 280 Points 250 badge 2x thumb

Posted 2 years ago

  • 1
  • 1
"Request"&Right("0000)" & ToText([Record ID#]),4)
Photo of Matthew Neil

Matthew Neil

  • 31,758 Points 20k badge 2x thumb
You are going to have to account for growing numbers.

Something like...
If([Record ID#]>99, ...
If([Record ID#]>999, ...
If([Record ID#]>9999, ...
Corrected formula

"Request"&Right("0000" & ToText([Record ID#]),4)