473,321 Members | 1,622 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

Putting numbered fields in order

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
3 1624
Rabbit
12,516 Expert Mod 8TB
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
159 Expert 100+
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
32,556 Expert Mod 16PB
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

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

Similar topics

6
by: John Burton | last post by:
I wrote a python program on windows which needs to listen for connections on a low numbered port which works fine on windows but on linux you need to be *root* in order to listen for connections on...
9
by: Paul Morrow | last post by:
I have seen the technique where a number of rows in a database are displayed in an html table so that each column of each row is editable. They use a single form surrounding the table, where each...
3
by: | last post by:
Hi, I have an unmanaged VC7 app calling a C# dotnet service. I'm using the VS2003 generated proxy class derived from CSoapSocketClientT. The client app works fine for most of my customers, but...
16
by: StenKoll | last post by:
Help needed in order to create a register of stocks in a company. In accordance with local laws I need to give each individual share a number. I have accomplished this by establishing three tables...
1
by: Grim Reaper | last post by:
I asked this question, earlier, and received an answer that I am not sure about how to do. Basically, I am printing mailing labels with a "Sorting/Grouping" section that groups the label types...
3
by: RR | last post by:
We have cards that are numbered consecutively. These cards are given out to different people in different sized batches. One group might get 5, the next group might get 20. What is a good...
24
by: rudranee | last post by:
hi there, can anyone tell me how to lines from a file which are odd numbered i.e. 1st,3rd,5th...lines. i tried incrementing file pointer by 2 (fp=fp+2) but it does'nt work Can someone give me...
11
imrosie
by: imrosie | last post by:
Hello Experts This is a hard one (I still speak newbie). An expert may think it's not a big deal, but, I am trying to replace my Main Order form with a subform because of the following: ...
9
by: joba | last post by:
Hi, I am building a program to calculate vacations for employees. Can anyone show me how to write a VBA code to differentiate even-numbered (Eg 2005) years from odd - numbered(Eg.2006) years. ...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.