Library of Congress Call Number Sorting

[akvbroek]

HELP!

I am trying to sort Library of Congress call numbers and it is driving me batty!

Here is the way it needs to sort...

First sort alphabetically by 1-2 letters

AG105 .F43 1989

Then numerically by 1-4 numbers

AG105 .F43 1989

Skip the period

AG105 .F43 1989

Alphabetically by 1 letter

AG105 .F43 1989

Numerically by 1-4 numbers

AG105 .F43 1989

Then numerically again by 4 numbers

AG105 .F43 1989

Is there a way to do this without splitting the call number into multiple fields? Is there an add-on that could be downloaded and installed for call number datatypes, anything?

Any help is appreciated.

 

[gemma-the-husky]

if all the numbers are the same size, and same format you shouldn;'t have a problem anyway, i don't think.

if the 105 bit is different lengths, then you may have an issue

--------
you will need to disassemble the string to form a string that you CAN sort on - which means getting it all the same length - which is straightforward enough

look at functions instr, mid, left, right, maybe split also

so AG105 .F43 1989 becomes AG105F431989
and AG32 .F3 1989 becomes AG032F031989

then all the strings are the same length, and a normal sort will work.

 

[akvbroek]

Thanks gemma-

However, I can not change the format of the call number by adding any characters, eliminating spaces or periods.

The library of congress system has very specific meanings that would be changed by doing that.

 

[Banana]

Then you'd have to deconstruct the strings as Gemma-The-Husky suggested using Left, Right, Mid, InStr.

Maybe even write a function to provide a sorting value.

Example in a query:

SELECT CallNumber, Title, SortCallNumber(CallNumber)
FROM Titles
ORDER BY SortCallNumber(CallNumber)

Definition for the function "SortCallNumber":

Public Function SortCallNumber(sInput As String) As Long

'Deconstruct the string and assign a arbitarty value to each columns 
'using position values to give more weight to leftmost portion and 
'less to right most portions then return a number for the query to sort on.

End Function

 

[gemma-the-husky]

yes, i dint mean change it - but if you write a query you can add a column that evaluates a string that CAN be used to sort on. - but you will need a function to generate the string

so in your query you have a column that looks like

sortstring: sortedcallnumber(callnumber)

and you need a function

function sortedcallnumber(callnumber as string)as string
'function to take in callnumber, process it, and return the massaged result
'in the function value
end function

 

[Niroth]

This will help you take out each part of the number, I named the field for the call number as "callNumber"

Dim periodP, sortP1, sortP2, sortP3, sortP4, sortP5

periodP = InStr(1, callNumber, ".") 'position of period
sortP1 = Left(callNumber, 2) 'the first 2 letter
sortP2 = Mid(callNumber, 3, periodP - 3) 'the 1-4 numbers in between the period and the first 2 letter
sortP3 = Mid(callNumber, periodP + 1, 1) '1 letter after the period
sortP5 = Right(callNumber, 4) 'last 4 numbers
sortP4 = Mid(callNumber, periodP + 2, Len(callNumber) - Len(sortP1) - Len(sortP2) - Len(sortP3) - Len(sortP5) - 1) 'the 1-4 numbers after the period and the single letter


To actually sort a single field using these criterias at once in code, I magine it's quite hard. Good luck with it.