In article <[log in to unmask]>, Stephen Ringle
<[log in to unmask]> writes
>
>In our database I maintain a field for accession number and also for
>sorting number.  These Sorting Numbers all have full 4-digit years,
>and leading zeroes where necessary to insure that a list sorted on
>this field would put accession numbers in proper order.  A straight
>alpha sort of the accession numbers themselves would put 78.2 after
>78.19 -not what we want.  So these accessions have sorting numbers
>of 1978.02 and 1978.19, and then they sort OK.

So, you're in schtuck if you acquire more than 99 objects in any one
year, huh? :)

We break our accession numbers (which go back to 1878) into 7 fields:
museum code (string), accession year (number), item number (number),
part number (number), sub-part letter (string), and part number (as
string so we cannot record ranges, such as "1-10") and then sort on the
whole bally lot. This might seem a bit of a sledge hammer to crack a
nut, but it means we can easily accommodate all the variations of
accession number we find in the last 120 odd years of records, before we
finally managed (almost) to standardise on one format. It also makes
finding, say, all the accessions for 1946 very easy by using a query on
the accesion year field. It saves all that hassle with leading zeroes
too. These fields are generated on the fly, using a short piece of code,
when a keeper edits an existing accession number or adds a new one.

cheers

Mick