Results 1 to 9 of 9

Thread: Call-sign sort

  1. #1
    3 Star Lounger
    Join Date
    May 2010
    Location
    Philadelphia, PA, USA
    Posts
    208
    Thanks
    3
    Thanked 27 Times in 26 Posts

    Call-sign sort

    I have two tables of members of two different amateur radio clubs in two different Access files. The first field in each table is the person's call sign. See sample below.

    I would like to sort them based on the following criteria: Call district, Suffix, Prefix.

    The problem is the non-uniformity of these call signs. All of them have one digit but it is not always in the same spot in the string. Plus the prefix and suffix can be of different lengths. There can be duplicates in each portion of the call sign, but the complete call signs are unique.

    I know I can accomplish this by creating 3 fields for these parts of the call signs and manually parsing them but it would be nice to be able to do this using the existing single call sign field.


    K7DR Prefix K, Call district 7, Suffix DR
    N2LAS Prefix N, Call district 2, Suffix LAS
    AE4X Prefix AE, Call district 4, Suffix X
    KB3MDS Prefix KB, Call district 3, Suffix MDS
    KB9ER Prefix KB, Call district 9, Suffix ER
    W3MDS Prefix W, Call district 3, Suffix MDS

    Sorted list

    N2LAS
    KB3MDS
    W3MDS
    AE4X
    K7DR
    KB9ER

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    With that sort of data, the standard sort routines really don't work. Do you need it sorted that way on numerous reports and forms, or is it just a single report or two. If you are dealing with the second case, you might consider using a query with calculated columns to separate out the position of the suffix using an IIF function and the String manipulation functions Left(), Mid() and Right(). If it is the former, then the only suggestion I can make is to create a special VBA sort function that gets invoked and calculates a dynamic sort value each time the table is updated - not a trivial task.
    Wendell

  3. #3
    Star Lounger
    Join Date
    Dec 2009
    Location
    Findlay, Ohio
    Posts
    57
    Thanks
    4
    Thanked 6 Times in 6 Posts
    From one ham to another ham:
    In your query create 3 extra fields after the callsign to split the call sign and sort each ascending.
    In your report only use the callsign field.
    In your query add the following fields (assuming 'callsign' is the field name in that table.
    pref: Left([callsign],PosOfFirstDigit([callsign])-1) << pref i.e. wb n or whatever
    dist: Mid([callsign],PosOfFirstDigit([callsign]),1) << district 0 1 2 3 4 so on
    last: Mid([callsign],PosOfFirstDigit([callsign])+1) << rest of the call sign after the district

  4. #4
    Star Lounger
    Join Date
    Dec 2009
    Location
    Findlay, Ohio
    Posts
    57
    Thanks
    4
    Thanked 6 Times in 6 Posts
    Here's the module:
    Code:
    Option Compare Database
    Option Explicit
    
    Public Function PosOfFirstDigit(strTest As String) As Long
    
       Dim i As Long
    
       PosOfFirstDigit = 0
    
       For i = 1 To Len(strTest)
           If Mid$(strTest, i, 1) Like "#" Then
             PosOfFirstDigit = i
             Exit For
          End If
       Next
    
    End Function
    Last edited by RetiredGeek; 2015-04-03 at 09:18. Reason: Added code tags

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    A further suggestion - there is a function in VBA that will find the first occurrence of a specified string within a given text string. It is called InStr() and you can read about it here.. It does the same basic thing as the function PosOffFirstDigit() function. However, it seems to me you want to test whether the character in question is numeric or not - are valid digits in the range from 1 to 9 (or 0 to 9). If so there is another function called IsNumeric() that will do the trick.
    Wendell

  6. #6
    3 Star Lounger
    Join Date
    May 2010
    Location
    Philadelphia, PA, USA
    Posts
    208
    Thanks
    3
    Thanked 27 Times in 26 Posts
    Thanks for the suggestions. I haven't worked with VBA yet but I have done coding in xBase.

    How do I get orangehat's code into the database? I would guess it has to be attached to the query.

  7. #7
    New Lounger
    Join Date
    Dec 2009
    Location
    Camarillo, CA, USA
    Posts
    4
    Thanks
    4
    Thanked 0 Times in 0 Posts
    If you are unfamiliar with VB, I encourage you to add the three fields and string manipulation provided by "orangehat" in a query. You would simply run this query and re-sort your database after making any additions or updates. This procedure will allow you to revise someone's call when they have a call sign change and still have your database provide you with the data sorted in the manner you desire. I have a similar requirement but sort by suffix and prefix-zone.

    Quote Originally Posted by orangehat View Post
    From one ham to another ham:
    In your query create 3 extra fields after the callsign to split the call sign and sort each ascending.
    In your report only use the callsign field.
    In your query add the following fields (assuming 'callsign' is the field name in that table.
    pref: Left([callsign],PosOfFirstDigit([callsign])-1) << pref i.e. wb n or whatever
    dist: Mid([callsign],PosOfFirstDigit([callsign]),1) << district 0 1 2 3 4 so on
    last: Mid([callsign],PosOfFirstDigit([callsign])+1) << rest of the call sign after the district
    Last edited by WendellB; 2015-04-12 at 09:57. Reason: Direct comment to original poster

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Quote Originally Posted by nate01pa View Post
    Thanks for the suggestions. I haven't worked with VBA yet but I have done coding in xBase.

    How do I get orangehat's code into the database? I would guess it has to be attached to the query.
    It's reasonably easy to do. Click on the modules in the Nav Pane (2007 and later) or the Database Window (2003 and older), and create a new module, copy the code suggested, and that should make it available in a query.
    Wendell

  9. #9
    3 Star Lounger
    Join Date
    May 2010
    Location
    Philadelphia, PA, USA
    Posts
    208
    Thanks
    3
    Thanked 27 Times in 26 Posts
    I have solved the problem.

    I created a Make Table Query with the first 4 columns as follows: Call (from the main Members table), prefix, district, and suffix. Prefix was defined as follows: IIf(IsNumeric(Mid(Members!Call,2,1)),UCase(Mid(Mem bers!Call,1,1)),UCase(Mid(Members!Call,1,2)))
    This works because there will be either one or two letters before the number.

    Similarly I defined District this way: IIf(IsNumeric(Mid(Members!Call,2,1)),Mid(Members!C all,2,1),Mid(Members!Call,3,1)) and Suffix this way: IIf(IsNumeric(Mid(Members!Call,2,1)),UCase(Mid(Mem bers!Call,3)),UCase(Mid(Members!Call,4)))

    Running this query produced the desired result.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •