VBA Code: Difference between revisions
Created page with "==(SPECIFY) Derive canonical name from FullName== Access-VBA in query: <br/> canonicalName: IIf(InStr(Nz([fullname]),":")>0,Mid([Fullname],InStr([fullname],":")+2),[Fullname])" |
No edit summary |
||
| Line 1: | Line 1: | ||
==(GENERAL) Transform author name abbreviation to TDWG standard== | |||
==(SPECIFY) Identify autonyms== | |||
Access VBA in query: Autonym: findOccurancesCount([FullName],[Name])<br/> | |||
VBA Function:<br/> | |||
Function findOccurancesCount(baseString, subString) As Integer<br/> | |||
baseString = Nz(baseString, " ")<br/> | |||
subString = Nz(subString, " ")<br/> | |||
occurancesCount = 0<br/> | |||
i = 1<br/> | |||
Do<br/> | |||
foundPosition = InStr(i, baseString, subString) 'searching from i position<br/> | |||
If foundPosition > 0 Then 'substring is found at foundPosition index<br/> | |||
occurancesCount = occurancesCount + 1 'count this occurance<br/> | |||
i = foundPosition + 1 'searching from i+1 on the next cycle<br/> | |||
End If<br/> | |||
Loop While foundPosition <> 0<br/> | |||
findOccurancesCount = occurancesCount<br/> | |||
End Function<br/> | |||
==(SPECIFY) Derive canonical name from FullName== | ==(SPECIFY) Derive canonical name from FullName== | ||
Access-VBA in query: <br/> | Access-VBA in query: <br/> | ||
canonicalName: IIf(InStr(Nz([fullname]),":")>0,Mid([Fullname],InStr([fullname],":")+2),[Fullname]) | canonicalName: IIf(InStr(Nz([fullname]),":")>0,Mid([Fullname],InStr([fullname],":")+2),[Fullname]) | ||
Revision as of 20:27, 9 April 2024
(GENERAL) Transform author name abbreviation to TDWG standard
(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])