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

i have text field in microsoft access consists of digits. don't know if these digits preceded blank characters or not.

what want if field has '1' want convert '0001', if has '89' want convert '0089', etc. meaning want make field consistent in length of 4 characters , pad number appropriate number of '0's.

how do this? can use calculated field approach? can convert database sql if sql has easy way this.

thanks.

you can use format() function transform string of digits. format() doesn't care whether or not digits preceded spaces.

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

if want display field values format in query:

select format([yourtextfield],"0000") yourtable; 

if want change how they're stored:

update yourtable set [yourtextfield] = format([yourtextfield],"0000"); 

edit: @onedaywhen suggested using check constaint or validation rule ensure table accepts valid data in future. here check constraint example:

alter table yourtable add constraint four_digits_required check (     yourtextfield '[0-9][0-9][0-9][0-9]'     ); 

you can run statement currentproject.connection.execute, ado object method. ddl statements check constraints can executed ado. not execute statement currentdb.execute, dao object method.

alternatively should work yourtextfield's validation rule property:

like "[0-9][0-9][0-9][0-9]" 

the validation rule approach allow use validation text property display message user when submitted values unacceptable:

"4 digits required." 

in situation validation text approach same displaying check constraint name when constraint violated. however, if wanted more detailed message, validation text better choice.