Find Uppercase letters

[Marco Brilo]

Hi All,
How would I use a query to find the first occurrence of an uppercase letter
in a string like this: nebria (noreonebria) hudsonica LeConte
so in this case I want to find the position of 'L'. This will be used to
update another field with the word 'LeConte'
Thanks

 

[Access101]

Let me know if this helps you on your way:

Sub GetFirstCap()

strCap = "gettheFirstcap"
intLen = Len(strCap)

For c = 1 To intLen
strChar = Mid(strCap, c, 1)
If Asc(strChar) >= 65 And Asc(strChar) <= 90 Then
MsgBox "Found it " & " " & strChar
c = intLen
End If
Next

End Sub

 

[John W. Vinson]

Hi All,
How would I use a query to find the first occurrence of an uppercase letter
in a string like this: nebria (noreonebria) hudsonica LeConte
so in this case I want to find the position of 'L'. This will be used to
update another field with the word 'LeConte'
Thanks

Erm. That's a bit tricky since queries aren't case sensitive! And aren't genus
names usually capitalized (Nebria hudsonica)? And what about uncapitalized
describer names (I can imagine something like Pasilla peruensis de la Torre)?

You'll need some custom VBA code to do this but at the moment I'm not sure
it's a well-defined problem!

 

[Clifford Bass]

Hi Marco,

If you are dealing in more than plain English letters, you might use
this which should work for any Unicode character that is a letter and that
can have upper and lower case versions:

Public Function GetFirstCapitalLetterPosition(ByVal strToSearch As String)
As Long

' Returns position of first capital letter or zero if none

Dim intUnicodeLower As Integer
Dim intUnicodeUpper As Integer
Dim lngIndex As Long
Dim lngLength As Long
Dim lngReturn As Long
Dim strCharacter As String

GetFirstCapitalLetterPosition = 0
lngLength = Len(strToSearch)
For lngIndex = 1 To lngLength
strCharacter = Mid(strToSearch, lngIndex, 1)
intUnicodeLower = AscW(LCase(strCharacter))
intUnicodeUpper = AscW(UCase(strCharacter))
If intUnicodeLower <> intUnicodeUpper Then
' A character capable of having upper and lower case
If AscW(strCharacter) = intUnicodeUpper Then
GetFirstCapitalLetterPosition = lngIndex
Exit For
End If
End If
Next lngIndex

End Function

There may be a better way, but this seems to work.

Clifford Bass

 

[Clifford Bass]

Hi Marco,

Oh, yes. Then you use the function in your update query. Something
like:

update SomeTable set SomeField2 = Mid(SomeField1,
GetFirstCapitalLetterPosition(SomeField1))
where GetFirstCapitalLetterPosition(SomeField1) > 0;

Clifford Bass

 

[Access101]

This is spectacular!

Regarding John Vinsons response, I have code that capitalizes correctly just
about everything, you know, stuff like this:

strName(0) = "henry viii"
strName(1) = "ronald macdonald"
strName(2) = "jim mcfinish"
strName(3) = "rob van dyke"

If you know this person, Jay Holovacs, here's his disclaimer on the code, so
I don't believe I can distribute it:

'************** Code Start *************
'This code was originally written by Jay Holovacs.
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Jay Holovacs
'

 

[Access101]

Oh, here it is:

http://www.mvps.org/access/strings/str0008.htm

 

[Marco Brilo]

Thanks, this code worked beautifully. I did modify it a bit because as
Vinson wrote the Genus name is in uppercase e.g. Nebria (Boreonebria)
hudsonica LeConte. , additionally there were some records with parantheses
with the old genus name and some that did not. I only wanted the last few
words which is the authority of the species name and is always in uppercase.
The authority in all cases only came after the genus and species name and if
present after the old genus name in the paranthesis.
Below is the code which looks for the paranthesis, if found it starts to
search for the first uppercase letter following the paranthesis, if it's not
found then it searches from the second character so that it does not pick up
on the uppercase genus name.
Thanks for the code, much appreciated. Thanks everyone else for you input
as well.
Marco

 

[Marco Brilo]

oops, here is the code:

Public Function GetFirstCapitalLetterPosition(ByVal strToSearch As String)

' Returns position of first capital letter or zero if none

Dim intUnicodeLower As Integer
Dim intUnicodeUpper As Integer
Dim lngIndex As Long
Dim lngLength As Long
Dim lngReturn As Long
Dim intStartSearch As Integer
Dim strCharacter As String
intStartSearch = InStr(1, strToSearch, ")")
If intStartSearch = 0 Then
intStartSearch = 2
End If
GetFirstCapitalLetterPosition = 0
lngLength = Len(strToSearch)
For lngIndex = intStartSearch To lngLength
strCharacter = Mid(strToSearch, lngIndex, 1)
intUnicodeLower = AscW(LCase(strCharacter))
intUnicodeUpper = AscW(UCase(strCharacter))
If intUnicodeLower <> intUnicodeUpper Then
' A character capable of having upper and lower case
If AscW(strCharacter) = intUnicodeUpper Then
GetFirstCapitalLetterPosition = lngIndex
Exit For
End If
End If
Next lngIndex

End Function

 

[Clifford Bass]

Hi Marco,

Glad to hear it does the job for you and that it could be a springboard
to something else. And you are welcome.

Clifford Bass

 

[Clifford Bass]

Hi,

Glad you like it! And thanks for the link to the code related to
capitalization. Currently not needed, but good to know it is there should
the need arise.

Clifford Bass