Thanks for the posts guys.
I have been looking into this a bit. Problem was i had REF0001,REF0002,REF0003 e.t.c. Then someone typed in REF999 instead of REF0999.
I didn't know about this problem until i entered REF10000 and thought i had lost the record but as you say it puts it after REF100.
As i have a lot of records obviously i don't want to change each one individually as would take forever.
I came across a function called RemoveAlphas which strips away the text then you can use format(fieldname,"000000").
However like you said will be easier if i have a text field called "REF" and then have an autonumber as the other field. I found people typed in the wrong number when entering REF so instead of say REF0542 somone put in REF0842 by mistake. Then next person would come along, not realise the mistake and just follow on with the next number REF0843 instead of REF0543.
I know the autonumber solution will not fix this but i see no other easy way.
This was my primary key field REF so each individual record is unique but obviously does not follow order due to peope's typing errors.
Looking back wish i had used autonumber but hindsight is a wonderful thing.
I guess there is no way to autoincrement an alphanumeric primary key