How do I add (?) A number to a textbox with '0'?

I have a textbox in Microsoft Access that only consists of numbers. I don't know if these numbers are preceded by blank characters or not.

I want to do this, if the field has "1", I want to convert it to "0001", if it has "89", then I want to convert it to "0089", etc. So I just want to make the field a matched length of 4 characters and enter a number with the corresponding number "0".

How should I do it? Can I use a calculated field approach? I can convert the database to SQL if SQL has an easy way to do it.



source to share

3 answers

You can use a function Format()

to convert a string of digits. Format()

doesn't care if spaces are preceded by spaces.

? Format("89","0000")
? Format("    89","0000")


If you want to display these field values ​​in this format in your query:

SELECT Format([YourTextField],"0000")
FROM YourTable;


If you want to change the way they are saved:

UPDATE YourTable
SET [YourTextField] = Format([YourTextField],"0000");


Edit : @oneday suggesting using a CHECK CONSTAINT or Validation Rule to ensure your table will accept valid data in the future. Here's a CHECK CONSTRAINT example:

ADD CONSTRAINT four_digits_required
    YourTextField LIKE '[0-9][0-9][0-9][0-9]'


You can run this statement from CurrentProject.Connection.Execute

, which is an ADO object object. DDL statements with CHECK constraints can only be executed from within ADO. So you cannot execute this statement from CurrentDb.Execute

, which is a method of the DAO object.

Alternatively, this should work for the YourTextField authentication rule property:

LIKE "[0-9][0-9][0-9][0-9]"


The validation rules approach will also allow you to use the Validation Text property to display a message to the user when the provided values ​​are not valid:

"4 digits required."


In this situation, the Validation Text approach is roughly the same as displaying the name of a CHECK constraint when that constraint is violated. However, if you need a more detailed message, the verification text may be a better choice.



You can try something like this:

RIGHT('0000' + TRIM(column), 4)




There are several options, here's another one:

Left("0000", 4 - Len(Cstr(Trim(column)))) & Cstr(Trim(column))


Sometimes the Len command returns the length minus 1 with numeric values, so Cstr should avoid this.



All Articles