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
You can use a function
to convert a string of digits.
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
, 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
, which is a method of the DAO object.
Alternatively, this should work for the YourTextField authentication rule property:
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.
source to share