Find first capital letter 1

[Flippertje]

Hi all,

i'm trying to find the first capital letter is a string whilst being in the querybuilder.

sinterKlaas should result in 7

So i mean something like a Instr functon but then for capital letters...

Anyone?

Many thanks!

grtz Flippertje

 

[mp9]

Write a VBA function like this and then use it in your query:

Function FirstCap(strIn As String) As Long
Dim lngLen As Long
lngLen = Len(strIn)
If lngLen > 0 Then
    Dim lngC As Long, lngPos As Long
    For lngC = 1 To lngLen
        If Asc(Mid(strIn, lngC, 1)) >= 65 And Asc(Mid(strIn, lngC, 1)) <= 90 Then
            FirstCap = lngC
            Exit Function
        End If
    Next
End If
End Function

Returns 0 if no upper case character, assumes strict English A-Z. FirstCap("sinterKlaas") returns 7. Put this function in a module and then use in a query like this:

SELECT FirstCap([FieldNameHere]) AS FirstCapital
FROM TableNameHere;

http://www22.brinkster.com/accessory/