How to add multiple delimiters to an MS Access field

[Gary Hnat]

Being in the travel business I have a field in a database that contains the trip itinerary showing the city codes all run together ... like JFKMIALAXSEA!

I need to still show one field but now need to separate each individual city code, which is 3 characters and enter a dash between them .... JFK-MIA-LAX-SEA!

I can get this to somehow work by using the left and mid function ...
Routing: Left([ITINERARY],3) & "-" & Mid([ITINERARY],4,3) & "-" & Mid([ITINERARY],7,3) & "-" & Mid([ITINERARY],10,3) & "-" & Mid([ITINERARY],13,3) & "-" & Mid([ITINERARY],16,3) & "-" & Mid([ITINERARY],19,3) & "-" & Mid([ITINERARY],22,3) & "-" & Mid([ITINERARY],25,3) & "-" & Mid([ITINERARY],28,3)

BUT /// I never know how many 3 letter city codes are run together as each trip is different. The problem is how to "not" show the extra dashes at the end of my new field by applying the above code!

Any suggestions would be most helpful

Thanks

 

[MajP]

Public Function SplitCities(Cities As Variant) As String
  Dim i As Integer
  If Not IsNull(Cities) Then
    For i = 1 To Len(Cities)
       SplitCities = SplitCities & Mid(Cities, i, 1)
       If i Mod 3 = 0 And i <> Len(Cities) Then SplitCities = SplitCities & "-"
    Next i
  End If
End Function

routing:splitCities([itinerary])

 

[Gary Hnat]

This solution works like a charm and thus eliminates all of the trailing dashes from my new Routing field.

Can't thank you enough! Most appreciated!

Gary