By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,039 Members | 1,831 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,039 IT Pros & Developers. It's quick & easy.

Putting numbered fields in order

P: 1
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.
Feb 15 '07 #1
Share this Question
Share on Google+
3 Replies


Rabbit
Expert Mod 10K+
P: 12,366
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.
Feb 15 '07 #2

MSeda
Expert 100+
P: 159
you can use an expression like:
Right(Value + 10000000, 7)
to add leading zeros. the example yeilds a seven digit string.
Feb 15 '07 #3

NeoPa
Expert Mod 15k+
P: 31,494
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]
Expand|Select|Wrap|Line Numbers
  1. SET [YourPK]=Format(Val([YourPK]),'00000') & Right([YourPK],3)
Feb 15 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.