Putting numbered fields in order

[Danzak]

Can anyone out there help me with a problem I’m having?

I have a database with 10117 records in it. Each record is numbered from 0001NHP to 10117NHP. Sometimes I have the database in alphabetical order of names and sometimes in number order. The problem is when I put it into number order. It runs in order until it gets to 0999NHP then it jumps to 10000NHP runs to 10009NHP then back down to 1000NHP then to 10010NHP which runs to 10019NHP the back to 10001NHP and so on, changing like that in intervals of 9
e.g.
0999NHP
10000NHP
10001NHP
10002NHP
10003NHP
10004NHP
10005NHP
10006NHP
10007NHP
10008NHP
10009NHP
1000NHP
10010NHP
10011NHP

If anyone knows a way of getting round this, can they please let me know?

Thank you.

[Rabbit]

Because of the way string comparisons are made, you're going to have to append leading zeroes.

More on this later unless someone beats me to the punch. Busy at work.

[MSeda]

you can use an expression like:
Right(Value + 10000000, 7)
to add leading zeros. the example yeilds a seven digit string.

[NeoPa]

Use this SQL (or similar - I don't have any name info) to update your data to a string format that works (As Rabbit says).
UPDATE [YourTable]

SET [YourPK]=Format(Val([YourPK]),'00000') & Right([YourPK],3)