order with strings and numbers

  • Thread starter Thread starter Lars
  • Start date Start date
L

Lars

I have a column with text. Some contain 'Yorkshire Archaeology 1:
name of the document', 'Yorkshire Archaeology 2: Different title',
'Yorkshire Archaeology 11: text', 'The Medieval Manor and Manorial
Mills.

When I order this on text base then the order will be
The Medieval Manor and Manorial Mills
Yorkshire Archaeology 1: name of the document
Yorkshire Archaeology 11: text
Yorkshire Archaeology 2: Different title

But i want it to look like
The Medieval Manor and Manorial Mills
Yorkshire Archaeology 1: name of the document
Yorkshire Archaeology 2: Different title
Yorkshire Archaeology 11: text

Thanks
Lars
 
Lars

I believe this is happening because your column is storing more than one
fact, and you wish to sort by one of those (internal) facts. Text sorts,
.... well, alphabetically. In an alphabetic sort, "1" comes before "11"
comes before "2".

If you need to sort by a numeric order, consider adding a field that holds
an integer on which you sort.

Or, if you are feeling particularly "frisky", you could try creating a
procedure that steps through each character in your text string, looking for
a numeric character, saving that number with the string, then using that
number to help you sort. This is a bit of work, and a work-around, given
that Access offers a much simpler approach (see above).

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
 
Lars

I believe this is happening because your column is storing more than one
fact, and you wish to sort by one of those (internal) facts. Text sorts,
... well, alphabetically. In an alphabetic sort, "1" comes before "11"
comes before "2".

If you need to sort by a numeric order, consider adding a field that holds
an integer on which you sort.

Or, if you are feeling particularly "frisky", you could try creating a
procedure that steps through each character in your text string, looking for
a numeric character, saving that number with the string, then using that
number to help you sort. This is a bit of work, and a work-around, given
that Access offers a much simpler approach (see above).

--
Regards

Jeff Boycewww.InformationFutures.net

Microsoft Office/Access MVPhttp://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentorhttp://microsoftitacademy.com/









- Show quoted text -
Click to expand...

I know. When the database was made the column should only have the
title (which is text). But then the added some volumes to the title.
Now they want to order it and alphabetical and numeric. I know why it
is happening but i try to find a way around it.

I can at a field for sorting, but then when I add an item to the
column I have to change all the orders as well, so that is not easy???
Or is there an easy way of updating the order (you still have the
problem of where the document should fit inthe document).
 
Lars

I guess that depends on your definition of "easy". If adding a
sort-by-integer field and re-ordering after each addition isn't "easy", then
you can create the procedure I outlined.

By the way, if you use integers like 10, 20, 30 (or 100, 200, 300, ...) you
will be able to intersperse quite a few "new" entries without having to
modify the sort values for your existing records...

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
 
Hi Jeff

Thanks for your reply. It was helpfull to discuss this matter. The way
I solved it was to copy the names in a new column called orderTitle
instead of title. I added a zero infront of the title (when needed)

so the new column looks like

The Medieval Manor and Manorial Mills
Yorkshire Archaeology 01: name of the document
Yorkshire Archaeology 02: Different title
Yorkshire Archaeology 11: text

when order by the orderTitle.
I know it is a duplication of data (although not quit) but it works
fine without a difficult procedures. When adding new documents I can
add the same title twice, or changed it for the order, in case there
are more volumes coming

Thanks again
 
Back
Top