I'd do a user defined function because I like user defined functions
but the logic could be incorporated into a Sub just as easily
SepChar is optional - I just put it in so I could see the pieces
Option Explicit
'The UNIQUE_ID number is an automatically generated attribute, only used internally to allow the Oracle database to identify each feature.
' The number is a combination of the GBR_ID, SUB_NO and FEATURE_C attributes.
'The GBR_ID is the feature identification number - e.g. Hamilton Island is 20-057.
'The SUB_NO is a three-digit number that identifies multiple features of the same type in a group,
' e.g. multiple reefs surrounding an island are labelled a, b, c, d etc. - these will be numbered 101, 102, 103, 104 etc.
' If it refers to a single feature it will be given the number 100. So for example Marquis Island which has an ID of 22-063b will have a corresponding sub number of 102.
'The FEATURE_C is an automatically generated three-digit number representing the type of feature eg mainland, island, cay etc. (100, 102, 103)
' Mainland features = 100
' Islands = 102
' Cays = 103
' Reefs = 104
' Rocks = 106 and 108"
Function AussieBear(GBR_ID As String, FEATURE As String, Optional SepChar As String = vbNullString) As String
Dim sGBR As String, sSUB As String, sFEATURE As String
sGBR = Left(GBR_ID, 2) & Mid(GBR_ID, 4, 3)
If Len(GBR_ID) = 6 Then
sSUB = "100"
Else
sSUB = Asc(UCase(Right(GBR_ID, 1))) + 36
End If
Select Case FEATURE
Case "Mainland"
sFEATURE = "100"
Case "Island"
sFEATURE = "102"
Case "Cay"
sFEATURE = "103"
Case "Reef"
sFEATURE = "104"
Case "Rock"
sFEATURE = "106" '???? and 108"
End Select
AussieBear = sGBR & SepChar & sSUB & SepChar & sFEATURE
End Function