I'm working in a library and we're creating a database of collection items,
but I can't seem to get Access to sort them by Library of Congress call
number correctly. Access sorts, for instance, S1.U556, S115.E32, S16.I6732 in
that order, rather than S1.U556, S16.I6732, S115.E32. Any ideas on how to
change the sort setting? Any idea how to create a new type of Access field
for this data that would sort correctly?

"William Gee" <William (E-Mail Removed)> wrote:

>I'm working in a library and we're creating a database of collection items,
>but I can't seem to get Access to sort them by Library of Congress call
>number correctly. Access sorts, for instance, S1.U556, S115.E32, S16.I6732 in
>that order, rather than S1.U556, S16.I6732, S115.E32. Any ideas on how to
>change the sort setting? Any idea how to create a new type of Access field
>for this data that would sort correctly?


Tables are not sorted, so use a query that selects the
appropriate fields from the table. Add a calculated field
like:
Expr: Val(Mid(LCnumfield, 2))
and set it to sort ascending.

You may need to refine this, but I don't know what it might
be.

--
Marsh
MVP [MS Access]

On Sat, 26 Feb 2005 14:57:02 -0800, "William Gee" <William
(E-Mail Removed)> wrote:

>I'm working in a library and we're creating a database of collection items,
>but I can't seem to get Access to sort them by Library of Congress call
>number correctly. Access sorts, for instance, S1.U556, S115.E32, S16.I6732 in
>that order, rather than S1.U556, S16.I6732, S115.E32. Any ideas on how to
>change the sort setting? Any idea how to create a new type of Access field
>for this data that would sort correctly?

As Marshall says, tables HAVE NO ORDER - they're unordered "heaps" of
data. You need to use a Query to sort records.

Access is sorting these values as single text strings; S1.U556 sorts
before S115.E32 because the character . is before the character 1 in
the collating sequence for strings. Access has NO way to know that you
*intend* the 1 and the 115 to be numeric values. I'm not sure what the
range of variation of these codes might be. If a valid call number
ALWAYS consists of two portions, each consisting of a single
alphabetic character followed by a number, you might want to consider
storing the call number in *four* fields rather than one: TextMajor
(Text 1), NumMajor (Integer), TextMinor (Text 1), and NumMinor
(Integer). S1.U556 would be stored as S, 1, U, and 556 respectively;
you can write an expression to concatenate the four fields for display
purposes. And they'll sort correctly.

To sort these values with the existing structure you'll need to use
some complex Mid() and InStr() expressions to parse out the letters
and numbers, and the sort will likely be VERY inefficient.

John W. Vinson[MVP]

If I remember my basic office skills correctly, Access is sorting the
values correctly by standard methods S115 would come before S16 since
the 3rd character '1' comes before '6'. Access is viewing the values as
TEXT as opposed to NUMBERS. The values that you need can be converted
from text to numeric by writting some code to extract the values and
convert them. The code should be relatively simply, but a bit longer
than I can come up with spur of the moment.

David H
Come on baby light my fire: www.spreadFireFox.com



William Gee wrote:
> I'm working in a library and we're creating a database of collection items,
> but I can't seem to get Access to sort them by Library of Congress call
> number correctly. Access sorts, for instance, S1.U556, S115.E32, S16.I6732 in
> that order, rather than S1.U556, S16.I6732, S115.E32. Any ideas on how to
> change the sort setting? Any idea how to create a new type of Access field
> for this data that would sort correctly?

"=?Utf-8?B?V2lsbGlhbSBHZWU=?=" <William (E-Mail Removed)>
wrote in news:381377FD-1DAE-4662-BA4D-(E-Mail Removed):

> Any idea how to create a new type of Access field
> for this data that would sort correctly?


SELECT Whatever
FROM Wherever
ORDER BY CInt(Val(Mid$(CallNumber,2))) ASC


Hope that helps


Tim F

"William Gee" <William (E-Mail Removed)> wrote in message
news:381377FD-1DAE-4662-BA4D-(E-Mail Removed)
> I'm working in a library and we're creating a database of collection
> items, but I can't seem to get Access to sort them by Library of
> Congress call number correctly. Access sorts, for instance, S1.U556,
> S115.E32, S16.I6732 in that order, rather than S1.U556, S16.I6732,
> S115.E32. Any ideas on how to change the sort setting? Any idea how
> to create a new type of Access field for this data that would sort
> correctly?

This question has been answered in another group to which you posted it
separately.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)