Good Afternoon,

I am hoping to get some help here. I have a table where one of the fields are a six digit sort code. I want to change the sortcode so that it is in the correct format. 12-34-56 rather than 123456. I have tried to just change the validation rule to Like "**-**-**" but this does not change the format for me.

Do I need to run an update query and if so how do I do it without changing the numbers to *.

I need to get this sorted urgently so any help would be appreciated.

Thanks

if you store it as text you could do an input mask.

if you need to update the field, you could use the following in a query:

Update to: left([fieldName],2) & "-" & left([FieldName,4) & "-" & right([FieldName],2)


Note that the field has to be a text field in order to do this.

but I dont think you should change it for storage purposes

do you store it as text or numbers - if the first digit could be a 0 then it should be text, otherwise number is probably better

you can just use format command whenever you need to display it

format(sortcode,"XX-XX-XX") {something like - not sure about the code to use - probably isnt an X, but I never use them}
format(sortcode,"00-00-00") (if its a number)

and use an input mask "XX-XX-XX" etc to input the data.