Call-sign sort

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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(Members!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!Call,2,1),Mid(Members!Call,3,1)) and Suffix this way: IIf(IsNumeric(Mid(Members!Call,2,1)),UCase(Mid(Members!Call,3)),UCase(Mid(Members!Call,4)))

Running this query produced the desired result. :rolleyes:

Reply | Quote

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

Reply | Quote

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.

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

Reply | Quote