Force Text Field To Always Be 4 Characters

[jo15765]

We have a table where the data is alphanumeric and can be between 2 - 4 characters. If the data is < 4 characters we want to append a 0 to the beginning of the string to force it to be 4 characters.

Example - 1b would become 001b or 11b would become 011b. Can you add an input mask on a query to do such or would I need to run an update query on the field to somehow append 1 zero the any string where Len = 3 and append 2 zero's to any string where Len = 2?

[pbaldy]

You can leave it as entered and display it with the Format() function. I suppose you could also use that function in the update event of the textbox and add the padded zeros there.

Format(FieldName, "0000")

[jo15765]

You can leave it as entered and display it with the Format() function. I suppose you could also use that function in the update event of the textbox and add the padded zeros there.

Format(FieldName, "0000")

I am actually using it as a control source for a report and want the report to display 4 digits. Would I be better off formatting in the query or report?

[June7]

Will it always be a 'b' or can there be other alpha characters? Always just the single alpha at the end?

InputMask property can be a bit aggravating, especially if you don't want to force users to enter the prefix zeros. VBA in the textbox AfterUpdate event can modify the value before it is committed to record.

If you need to modify existing data, will be a little complicated because of the alpha.

[pbaldy]

It probably doesn't matter; I'd probably do it on the report.

[jo15765]

Will it always be a 'b' or can there be other alpha characters? Always just the single alpha at the end?

InputMask property can be a bit aggravating, especially if you don't want to force users to enter the prefix zeros. VBA in the textbox AfterUpdate event can modify the value before it is committed to record.

If you need to modify existing data, will be a little complicated because of the alpha.

Yes - it would be existing data. We have a button press event on a form that imports an Excel spreadsheet into a table. So the data is imported however it is sent.

No, "b" will not always be the first example, those were just the first two I saw in the table when making this post so I used for example.

[jo15765]

It probably doesn't matter; I'd probably do it on the report.

Would I do such by setting the Input Mask of the text box that has the field as the control source?

[pbaldy]

A control source of:

=Format(...)

with the text above.

[Micron]

If you used Format([MyField],"0000") in an update query, it might work if the values began with letters, but I think it would alter any value that began with a number to become 0000. The problem with trying to do this in an after update event on a form is that you'd have to leave the record in order to update it, otherwise you'd get a lock violation error - unless maybe the option for no locks was set.

[jo15765]

A control source of:

=Format(...)

with the text above.

Set the control source to Format([MyField],"0000")

[pbaldy]

?format(123,"0000")
0123
?format("123","0000")
0123

[pbaldy]

Set the control source to Format([MyField],"0000")

With the = I mentioned, yes.

[jo15765]

With the = I mentioned, yes.

Perfect, that got it! Thank you!

[pbaldy]

Happy to help!

[Micron]

=Format([MyField],"0000")

I was trying that because I figured it should work in the report and was surprised at what I got. With 3 records (1a, 2b, 3c) in a short text field (default 255 character limit, no table level formatting) and an additional textbox on the report with the expression as above, I got this for 3 records (left control bound to table, right control with the expression):

1a 0000
2b 2b
3c 3c
What the heck?? Just in case the 1 was an issue, I changed it to 4a in the table and got the same result - 0000. OK, so what if I was right about the values starting with numbers versus letters? Changed 4a to a4 and got a4. So why does the solution seem to work for the OP??