Changing Data Format in Table

[Sharkiness]

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

 

[Kryst51]

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

 

[Scooterbug]

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.

 

[gemma-the-husky]

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.