Solved

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

Posted on 2013-06-18
3
292 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

734 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