Solved

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

Posted on 2013-06-18
3
293 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 48

Accepted Solution

by:
Dale Fye 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:Brendt Hess
Brendt Hess 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

623 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