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.