Solved

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

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

770 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