Difference between revisions of "VBA Code"
From TETTRIs
(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])") |
|||
| (5 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
| + | ==(GENERAL) Transform author name abbreviation to TDWG standard== | ||
| + | Access query (in Excell use Substitute instead of Replace): <br/> | ||
| + | standardAuthor: Replace(Replace(Replace(Replace([Author],". ","."),".ex",". ex"),".in",". in"),".&",". &") | ||
| + | |||
| + | ==(SPECIFY) Identify autonyms== | ||
| + | Access VBA in query: isAutonym: IIf(findOccurancesCount([CanonicalName],[Name])>1,True,False)<br/> | ||
| + | VBA Function: [https://stackoverflow.com/questions/14286505/how-to-find-the-number-of-occurrences-of-a-substring-within-a-string-vb-net Source]<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]) | ||
| + | |||
| + | ==(SPECIFY) Create fullNameWithStandardAuthor== | ||
| + | Access-VBA in query: (Omits author citation for autonyms)<br> | ||
| + | fullNameWithStandardAuthors: IIf([isAutonym],Nz([canonicalName]),Nz([canonicalname])+" "+Nz([standardauthor])) | ||
Latest revision as of 15:18, 10 April 2024
Contents
(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: isAutonym: IIf(findOccurancesCount([CanonicalName],[Name])>1,True,False)
VBA Function: Source
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])
(SPECIFY) Create fullNameWithStandardAuthor
Access-VBA in query: (Omits author citation for autonyms)
fullNameWithStandardAuthors: IIf([isAutonym],Nz([canonicalName]),Nz([canonicalname])+" "+Nz([standardauthor]))
