Solved

Sort Challenge - How to get Access to sort the way I want.

Posted on 2013-06-18
3
289 Views
Last Modified: 2013-06-18
Sort challenge,  I'm wondering if there is a better way to accomplish a requirement.

The requirement is to present a list of connections by termination cabinet, panel strip and junction point. The data is all stored in an Access table and I retrieve it with a simple query.

Select CableID, Cabinet, Panel, Junction from Connections order by Cabinet, Panel, Junction

The issue is that the fields are strings which means they sort like this:

SortExample1
Rather than like this

Sort 3
Temporarily I altered the Cabinet slightly adding a leading 0 to the string which results in a successful output.

Sort4
The problem is that the data is synchronized with another source and I can't change the value in the source. So when the sync routine runs it identifies each of the changed values as a conflict.

I was hoping I could use the format function but it only works with pure numeric data.
 
If Cabinet =  "123" or 123 then Format([Cabinet],"0000") returns 0123 but
If Cabinet =  "1M" then Format([Cabinet],"0000") returns 1M not 001M as I was hoping.

I thought about parsing off the last character i.e.  Left([Cabinet],len([Cabinet])-1) but Cabinet is not a consistent size.  

1,1M and 1ML are all valid Cabinet designations.  

I also thought about adding the 0 to the front i.e. "0"&[Cabinet] but I have the same issue 1M and 1ML would need the 0 but 11M would not and 1ML and 11M are the same length.

So I'm looking for other ideas,
0
Comment
Question by:11ptMan
3 Comments
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 250 total points
ID: 39257692
Because of the variability in the [Cabinet] field, I think the best way to approach this would be to create a custom function to return a common format for the value in the [Cabinet] field, something like:
Public Function fnCabinet(Cabinet As String) As String

    Dim intLoop As Integer, intPos As Integer
    
    For intLoop = 1 To Len(Cabinet & "A")
    
        If Mid(Cabinet & "A", intLoop, 1) Like "[a-z, A-Z]" Then Exit For
    
    Next
    
   fnCabinet = Format(Left(Cabinet, intLoop - 1), "000")
   If intLoop <= Len(Cabinet) Then fnCabinet = fnCabinet & Mid(Cabinet, intLoop)

End Function

Open in new window

This will return a string with three numeric characters followed by the appropriate alpha characters.

Then, in your OrderBy clause use:

ORDER BY fnCabinet([Cabinet]), Panel, Junction.
0
 
LVL 32

Assisted Solution

by:bhess1
bhess1 earned 250 total points
ID: 39257809
One way to do this is to justify the data yourself.  For example, if the Cabinet field was up to six characters long, you could try:

Select CableID, RIGHT(SPACE(6) & Cabinet, 6) as Cabinet, Panel, Junction from Connections order by RIGHT(SPACE(6) & Cabinet, 6), Panel, Junction
0
 

Author Closing Comment

by:11ptMan
ID: 39257904
Both solutions resolve the issue and each has its advantages and disadvantages.

Fyed's solution is a bit more complex and while negligible takes longer to execute. On the plus side it does provide the leading 0's and could accommodate a string longer that 6 characters without modification.

Bhess1's solution is much simpler but does rely on the original data not being more than 6 chars in length.

So in a spirit of fairness I'm splitting the points between you.

One enhancement I thought about was using the String(6,"0") in place of the Space(6) in the query portion if you wanted the leading 0's to appear.


Thank you both for your quick response.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now