CODEWITHSUNDEEP
sqlms-access
RJIGO
RJIGO

Reputation: 1943

How to prepend(?) number in text field with '0'?

I have a text field in Microsoft Access which consists of only digits. I don't know if these digits are preceded by blank characters or not.

What I want to do is 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. Meaning I just want to make the field consistent in length of 4 characters and pad the number with the appropriate number of '0's.

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

Thanks.

Upvotes: 3

Views: 969

Answers (3)

HansUp
HansUp

Reputation: 97101

You can use the Format() function to transform the string of digits. Format() doesn't care whether or not the digits are preceded by spaces.

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

If you want to display those field values with that format in a query:

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

If you want to change how they're stored:

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

Edit: @onedaywhen suggested a using CHECK CONSTAINT or Validation Rule to ensure your table only accepts valid data in the future. Here is 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 that statement from CurrentProject.Connection.Execute, which is an ADO object method. DDL statements with CHECK constraints can only be executed from ADO. So you could not execute that statement from CurrentDb.Execute, which is a DAO object method.

Alternatively this should work for YourTextField's Validation Rule property:

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

The Validation Rule approach would also allow you to use the Validation Text property to display a message to the user when submitted values are unacceptable:

"4 digits required."

In this situation the Validation Text approach is about the same as displaying the CHECK constraint name when that constraint is violated. However, if you wanted a more detailed message, the Validation Text could be a better choice.

Upvotes: 6

Matt Donnan
Matt Donnan

Reputation: 4993

There are a few variations, here is another:

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

Sometimes the Len command returns the length minus 1 with numeric values so the Cstr is there to avoid this.

Upvotes: 1

Pepe
Pepe

Reputation: 6480

You can try something like this: 

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

Upvotes: 3

Related Questions