Alphanumeric auto numbering using decimal system

[Ben]

Sure you COULD get Access to do this using a form and
some code, but the real question is why?
Autonumber/identity fields are there to provide a way to
ensure unique keys, they aren't supposed to mean
anything, and no one is really supposed to see them, nor
sort on them, they're helpful 'behind the scenes'.

If you really want to, you'd have to change the field
from autonumber to a single,double,currency or decimal
datatype and use code in a form to figure out what the
value is for the preceding and succeeding fields - what
happens when you get one that falls 194 and 194.1? Your
stuck with 194.11, if it happens again you've got 194.111
ad nauseum. If you are just doing this to keep them
sorted, your better off using a query to sort them based
on title, then it doesn't matter where/when they are put
in the data table, you still see them in alphabetical
order.
HTH,
Ben

 

[Nessie]

I need them numbered so the techs can place them back on the stacks
properly, otherwise it would be a big mess. The books are stored on
the shelves alphanumerically therefore the "index" report I generate
and keep on the shelves has to show the same order. I've looked at
several library management software packages and book collection apps
etc. and none have automatic numbering either so I guess I'll just
continue to number them manually. Thanks.

JR

 

[Ben]

I wish I could offer some sort of solution, but I think
I'm confused. Are you saying that your techs can count
but not spell? (I've worked with various sorts of techs
before and many of them can even spell thier own names as
well as assorted 4 letter words!) What kind of "index
report" are you creating? Do you only wish to do this so
you can create handy labels that make it easy to see
where things go? It may do you well to re-think your
problem a bit.
The reason you have not found library management systems
that do what you are looking for is becuase, if they did,
the reference numbers would end up like 3.1415927.....
becuase there will ALWAYS be something that ends up in
between two existing values. Even the Dewey decimal
system only uses the numbers for categories, and arranges
books alphabetically within a numbered category. It makes
no sense to 'manufacture' an index value when a perfectly
good index already exists - the author's name or the
book's title.
Or maybe you're referring to manual titles with catchy
names like my stereo's, such as KXY-1332. In this case
you can sort on the colum and get correct results as long
as the data is consistant. Ordering on a alphanumeric
column will do ASCII ordering, so uppercase 'Z' comes
before lowercase 'a'. To avoid non-alphabetic ordering
you could UCase everything before adding it to the table
so you get consistant results. I'm guessing from your
description this is what you are going for.
HTH, maybe if you describe your issue and data in a more
detail the group can offer some more creative answers.
Ben

 

[Nessie]

The guys are always in such a rush that they don't take care to put
the manuals back alphabetically when the manuals have similiar titles.
For example, we have 200 books that are OM manuals for just the Air
Force Academy, but different locations around the base. Putting a
number on the spine increases the chances that the book will wind up
somewhere close to where it belongs on the shelf..lol. The index is
just a report with a few fields like title, location, book number etc.
that I keep at hand so they can find the book they need. These are
service type manuals.

Anyhow, you are correct, there is always going to be a book that falls
between existing books. My problem is keeping the decimal numbering
consistant throughout the table. Sometimes when this happens I'll add
a *.01 or use a *.01A or so forth. Then I loose track of what/how I
was doing the decimals.....it's CRS at it's worse. That's why I wanted
something to do this automatically. Thanks.

JR