Difference between revisions of "VBA Code"

Revision as of 21:29, 9 April 2024

(GENERAL) Transform author name abbreviation to TDWG standard

Access query (in Excell use Substitute instead of Replace):
StandardAuthor: Replace(Replace(Replace(Replace([Author],". ","."),".ex",". ex"),".in",". in"),".&",". &")

(SPECIFY) Identify autonyms

Access VBA in query: Autonym: findOccurancesCount([FullName],[Name])
VBA Function:
Function findOccurancesCount(baseString, subString) As Integer

   baseString = Nz(baseString, " ")

   subString = Nz(subString, " ")

   occurancesCount = 0

   i = 1

   Do

       foundPosition = InStr(i, baseString, subString) 'searching from i position

       If foundPosition > 0 Then                       'substring is found at foundPosition index

           occurancesCount = occurancesCount + 1       'count this occurance

           i = foundPosition + 1                       'searching from i+1 on the next cycle

       End If

   Loop While foundPosition <> 0

   findOccurancesCount = occurancesCount

End Function

(SPECIFY) Derive canonical name from FullName

Access-VBA in query:
canonicalName: IIf(InStr(Nz([fullname]),":")>0,Mid([Fullname],InStr([fullname],":")+2),[Fullname])