Member Avatar for DeanMSands3

I have a calculated field that takes the ID field of a table and pads it with leading zeroes.
So far, I've gotten this to work:
String$(4-Len(Trim$(Str$([ID]))),"0") & Trim$(Str$([ID]))
Broken down, it takes the ID field, and string-ifies it.
For some reason, this pads it with a space, hence the seemingly superfluous Trim$.
The Len function takes the count of the characters in the string.
This subtracted from 4 (I want 4 total digits).
The String$ function creates a set of zeroes the length of 4 minus the length of the ID number.
This concatenated back onto the trimmed, string-ified ID.
It works. It just feels like a freakish kludge.
I can't use the Format function in calculated fields, so this is what I've got.
You got anything better, DaniWeb? I sure would like to know.

EDIT: I just noticed I ought to add in some Modulus's to the formula. This is OK as the records in this one get purged annually and we'll never get over 10000 in a single year.

Edited by DeanMSands3
  • 2 Contributors
  • 1 Reply
  • 771 Views
  • 2 Hours Discussion Span
  • Latest Post Latest Post by adam_k
Member Avatar for adam_k

Isn't this the same as right([ID] + 10000;4) ?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.