Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 299
  • Last Modified:

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

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
11ptMan
Asked:
11ptMan
2 Solutions
 
Dale FyeCommented:
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
 
Brendt HessSenior DBACommented:
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
 
11ptManAuthor Commented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now