We have a field in a query that should be left-padded with zeroes if it is too short, and we accomplish this using the Format() function. However, there are some values that produce bizarre results.
Format("14425112-8","00000000-00")
Returns the value "00019330-78"
For most inputs, the string gets formatted as expected, 8 digits, hyphen, two digits. But in a few rare cases, the value is modified. Is this repeatable for anyone else? Does anyone have an explanation?
Thanks for your help.
This is an example of access trying to be too helpful. It looks like it is interpreting these values as dates, but since you didn't use any date indicators in the format e.g: (dd,mm,yyyy), it converted 1-1 to a date, and then tried to display it in decimal form:
debug.print Format("1-1","000000-00")
returns 000427-36 which is the decimal value 42736 which, if you convert to a date, becomes 1/1/2017. This is what access interpreted "1-1" as.
it seems that access has reserved the - character as symbolizing a date format, despite what their website says. This function is only useful for formatting actual dates, or numeric values, such as prices. If you are set on using the format function, you will have to change you separator to a decimal point, which apparently is the only character that will get you what you want with the leading and trailing zeros.
Otherwise, you may have to build your own function for this.
You cannot format a string like a number this way. Try this:
PaddedNumber = Right(String(8, "0") & "14425112-8", 10)
