Ok.. did that..

When I ran the query on the data that causes the error I got:

Unexpected Error Message

Error 5 Occured
Invalid proedure call or argument

cLetter value:
cLetterASC Value:77

and the same for Value:67 and Value:79

What does this mean?

Hi,

It looks like the ASC value remains unchanged from the previous character. The cLetter variable is Empty???? I think that a non standard character has been put in the cLetter variable, the next line tries to get the ASC number and fails because of this.

The function needs to recognise standard and non-standard ASCII characters before the ASC function call.I am looking into how to do this. Perhaps a member here can advise how to do this. I will put up a new post.

Jon

Akb786,

Put this check in between the two variable assignements.

    cLetter = Mid(strText, L, 1)
If IsNull(cLetter) Then
        FindUppers = False
        Exit Function
    end if
    cLetterASC = Asc(cLetter)

Don't forget, add this to both of these Sections in the routine.

Jon

Hi Joe,

It still does the same thing..

code now is:

Function FindUppers(strText As String, WholeWordOnly As Boolean) As Boolean
On Error GoTo ErrTrap

Dim cLetter As String
Dim cLetterASC As Long
Dim cWord As Boolean

Dim L As Integer

For L = 2 To Len(strText)

    cLetter = Mid(strText, L, 1)
    If IsNull(cLetter) Then
        FindUppers = False
        Exit Function
    End If
    cLetterASC = Asc(cLetter)

    Select Case cLetterASC
        Case 65 To 90 'Cap Found
            If WholeWordOnly Then

                'And check that the letter is not an I
                If Asc(Mid(strText, i + 1, 1)) <> 73 Then
                    Select Case Asc(Mid(strText, i + 1, 1))
                        Case 32, 33, 44, 45, 46, 47, 58, 59, 63, 95
                            Exit Function
                    End Select
                End If

                L = L + 1 'Next Letter
                'Loop forward through the rest of the word. If a lowercase is found then clear the cWord var
                Do While i < Len(strText)

                    cLetter = Mid(strText, L, 1)
                    If IsNull(cLetter) Then
                    FindUppers = False
                    Exit Function
                    End If
                    cLetterASC = Asc(cLetter)

                    Select Case cLetterASC
                        Case 32, 33, 44, 45, 46, 47, 58, 59, 63, 95
                            L = L - 1 'Move back one letter so Current L is examined (punctuation,space)
                            Exit Do
                        Case Else
                            If cLetterASC >= 65 And cLetterASC <= 90 Then
                                cWord = True
                            Else
                                cWord = False
                                Exit Do
                            End If
                    End Select
                    L = L + 1 'Next Letter
                Loop
            Else 'Find a Partially Capped word with no punctuation preceding and NOT full capped word
                i = L - 1

                'First Check that next letter (+2) is not uppercase
                If Asc(Mid(strText, i + 2, 1)) >= 65 And Asc(Mid(strText, i + 2, 1)) <= 90 Then Exit Function
                'And check thet the letter is not an I
                If Asc(Mid(strText, i + 1, 1)) = 73 Then Exit Function

                Do While i >= 1

                    cLetter = Mid(strText, i, 1)
                    If IsNull(cLetter) Then
                    FindUppers = False
                    Exit Function
                    End If
                    cLetterASC = Asc(cLetter)

                    Select Case cLetterASC
                        Case 32
                            'Do nothing
                        Case 33, 44, 45, 46, 47, 58, 59, 63, 95 'Punctuation
                            Exit Do
                        Case Else
                            cWord = True
                            Exit Do
                    End Select
                    i = i - 1 'Next Letter
                Loop
            End If

        Case 32, 33, 44, 45, 46, 47, 58, 59, 63, 95
            'Check that the cWord variable has not been previously populated (Whole Word)
            If cWord Then
                FindUppers = True
                Exit Function
            End If
    End Select
Next L

If cWord Then FindUppers = True
ExitTrap:
 Exit Function
ErrTrap:
    If Err.Number <> 0 Then
   'Debug.Print cLetter & ", ASC value:" & cLetterASC
    MsgBox "Error #" & Err.Number & " Occurred " & vbCr & Err.Description & vbCr & vbCr & "cLetter value:" & cLetter & vbCr & vbCr & "cLetterASC Value:" & cLetterASC _
    , vbExclamation + vbOKOnly _
    , "Unexpected Error" _
    , Err.HelpFile _
    , Err.HelpContext
    Resume ExitTrap
    End If
End Function

Try this -

    cLetter = Empty
    cLetter = Mid(strText, L, 1)
    If IsEmpty(cLetter) Then
        FindUppers = False
        Exit Function
    End If
    cLetterASC = Asc(cLetter)

Same again..

PMJIH (pardon my jumping in here)

Aren't you going to a lot of trouble when you're converting Asc?

Why not use the UCase and LCase commands in your comparisons? And don't forget to remove the "Option Compare Database" from the module -- otherwise case tests don't matter!

To see if the string has uppercase at all, use this statement:
Then to find your uppercase word, as you are walking throught the string one letter at a time use a test like: Definitely be sure to remove the "Option Compare Database"!

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org