Clever Geek Handbook

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.

Thank.

+3


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")
0089
? Format("    89","0000")
0089

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:



ALTER TABLE YourTable
ADD CONSTRAINT four_digits_required
CHECK (
    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.

+6


source


You can try something like this:



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


source


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.

+1


source






More articles:


All Articles