numbering in ascending order

[chrisvic1]

I'm working with Access 2002

I'm working on a database where I input information for subjects in a
research study. Each subject gets a special ID number (eg RNF 001, RNF 002,
RNF 003, etc.) I now have 1003 subjects. When I sort the Study ID field in
ascending order it does this: ...RNF 099, RNF 100, RNF 1000, RNF 1001, RNF
1002, RNF 1003, RNF 101, RNF 102, RNF 103... But the ID numbers in the 1000
range should be ordered after RNF 999. Is there any way I can get the ID
numbers in the one thousand range to be ordered sequentially after RNF 999
instead of RNF 100?

 

[Lars]

Hi

My guess is that the database sors the rows according to the string value.
For strings "RNF 101 > RNF 1001" is true. My suggestion is to skipp the RNF
(if ou can) and convert the field to an integer field.
As an option you could store every number as four digit string.

RNF 0001..RNF0009,
RNF 0010....


Lars

 

[KARL DEWEY]

You might try a sort field like this --
SortField: Val(Mid([ID],5,99))

 

[Jeff Boyce]

Combining "more than one fact in one field" (your "RNF" and your sequence
number) violates one of the basic precepts of database design (i.e., "one
fact, one field").

If ALL of your "ID"s start with "RNF", there's no need to store that, since
you can use a query to concatenate: "RNF" & [sequencenumber].

Regards

Jeff Boyce
Microsoft Office/Access MVP