Hi All, im new here and hope I have the right forum

I need some help please..

In an Access db containing messages of conversations from an online chat system, I need to be able to retrieve message that contain capital letters ie.. 'Hello I would LOVE some help' this message has a word that is written in capitals so I would like the message and the username (who sent the message) displayed as a query/form.

I have the following code which was provided to me by someone in a forum but do not fully understand how to make it work, I understand what it does but am not sure how it all should link in my db.

the code is :

Sub test()

Dim res

res = findUpper("a string with no uppers")

MsgBox res

res = findUpper("a string With one upper")

MsgBox res

res = findUpper("a string WIth two upper")

MsgBox res

res = findUpper("a TRUE upper case word")

MsgBox res



End Sub

Function findUpper(mStr) As Boolean

Dim i, length, holdletter, holdword

For i = 1 To Len(mStr)

holdletter = Mid(mStr, i, 1)

If Asc(holdletter) > Asc("A") And Asc(holdletter) < Asc("Z") Then ' if i'm a lower case letter

'add this character to a holding place

holdword = holdword & holdletter

Else

Select Case Asc(holdletter)

Case 32, 33, 44, 45, 46, 47, 58, 59, 63, 95 'punctuation, space indicates end of word

If Len(holdword) > 1 Then 'I have at least two letters that were caps in holdword

findUpper = True

Exit Function

End If

Case Else ' not end of word and not upper

holdword = ""

End Select

End If

Next i

findUpper = False

End Function


I was told to save this as a module and run the sub test function... but dont understand how to get this function to run and if it will do what i need. I need it to look through the Message field in the Table and then display the results on the form. Would anyone be able to explain how to do this..

I dont know much about access,vb and have been given this project so want to get this done but understand and learn as I go along.. please help

many thanks
-x-

Hi AKB786,

Welcome to the wrox P2P forum!

The routine you received does work but would need some modifcation of r a couple of reasons.

What we need to do is create a function similar to the one you have supplied. Find below a modified version of your original function.

The function below requires a string to evaluate and an optional precision integer that determines how many characters indicate an uppercase word. You can leave the Precision variable empty if you wish, the default is 2. i.e Happy would Not be classed as uppercase but HAppy would be classed as uppercase. It should be a nice simple function for you to get familiar with.

Code Start ----------

Function FindUpper(str As String, Optional Precision As Integer) As Boolean
'PURPOSE - Return a boolean indicating whehter a string contains capitalized characters
'EXPECTS - a string to inspect, an optional precision value
'RETURNS - True if str argument contains caps characters, False if no Caps found
'NOTE - Delete line 30 to include partially capped strings

    Dim i As Integer 'Holds current Character position
    Dim intCountUppers As Integer 'Total of Uppercase letters

    On Error Resume Next
    'If No Precision supplied then set at 2 chars
    If Precision = 0 Then Precision = 2

    For i = 1 To Len(str) 'Iterate throught the string
        'Store ref to the current character in string
        strCurLetter = Mid(str, i, 1)
        'Examine the Character for Uppercase format
        If Asc(strCurLetter) >= Asc("A") And Asc(strCurLetter) <= Asc("Z") Then
            intCountUppers = intCountUppers + 1 'increment the upper count
        Else
            Select Case Asc(strCurLetter)
                Case 32, 33, 44, 45, 46, 47, 58, 59, 63, 95 'punctuation, space indicates end of word
                    'If the Count of uppers = the Precision then assume have found an uppercase word
                    If intCountUppers >= Precision Then
                        FindUpper = True 'Just return a true and exit
                        Exit Function
                    End If
                    intCountUppers = 0
                Case Else
                    intCountUppers = 0 'DELETE this line to include partially capped letters
            End Select
        End If
    Next i

    If intCountUppers >= Precision Then FindUpper = True Else FindUpper = False

End Function

Code End ----------

Step 1 - Create the function

In the left side of the database window you will see an object called Modules. Click this. Click New Module. Once the Code window has opened paste the above code into the module. Close the Module and save.

Step 2 - Use the function in your query

Access allows you to use your User Defined Functions (UDF's) in many areas of the database including your queries. If you open a new query in design view you can use the expression builder to access your UDF's. In your case you would add the messages table to the query, locate the field that contains the message, and use the function on it to determine whether part of the message is uppercase.

Here is a basic SQL statement that does illustrates this.

SELECT FindUpper([Message]) AS HasUppers, tbl_Messages.*
FROM tbl_Messages
WHERE (((FindUpper([Message]))=True));

I have used a table called tbl_Messages with a text field called Message holding various scentences. (Uppercase, Lowercase and mixtures) Just rename the table and field string to whatever you need.

Please post back if you require clartification on any thing.

Jon

Hi Jon

I am speechless.. just wanted to say thank you so much. You have been amazing.. everything works perfectly and I understand it aswell, its all been explained really clearly.

I've tried lots of things and tried other forums too but got no help really and have been really really impressed by this.

Thanks alot once again.

:D

Hi Jon.. me again..

You have inspired me to try something else now..

As i said everything works perfectly.. but how would I modify the code to be able to get words within the message field which start with a capital letter.. obviously all words after a . (full stop) should begin with a capital letter so i dont want those, but any words within a message which begin with a capital letter.

As far as I understand it.. I need it to ignore any Capitals directly after a full stop.. but otherwise retrieve the message.. I think it needs an if statement saying something like IF the i (next character) is after a full stop then ignore.. otherwise.. carry on?

is this possible?

Hi

Replace the main part of the function with this snippet.

Code Starts - - -

    For i = 1 To Len(str) 'Iterate throught the string
        'Store ref to the current character in string
        strCurLetter = Mid(str, i, 1)
        'Examine the Character for Uppercase format
        If Asc(strCurLetter) >= Asc("A") And Asc(strCurLetter) <= Asc("Z") Then
            If Precision = 1 And i > 1 Then 'Check for Capitalized letters with no Full stop preceding
                x = i
                'Loop back thru the string to find full stop
                Do While x > 1 And Asc(Mid(str, x, 1)) <> 46
                    x = x - 1
                    If Asc(Mid(str, x, 1)) = 32 Then 'Space so decrement the X variable
                        x = x - 1
                    ElseIf Asc(Mid(str, x, 1)) = 46 Then
                        Exit Do
                    Else
                        FindUpper = True
                        Exit Function
                    End If
                Loop
            Else
                intCountUppers = intCountUppers + 1 'increment the upper count
            End If
        Else
            Select Case Asc(strCurLetter)
                Case 32, 33, 44, 45, 46, 47, 58, 59, 63, 95 'punctuation, space indicates end of word
                    'If the Count of uppers = the Precision then assume have found an uppercase word
                    If intCountUppers >= Precision Then
                        FindUpper = True 'Just return a true and exit
                        Exit Function
                    End If
                    intCountUppers = 0
                Case Else
                    intCountUppers = 0 'DELETE this line to include partially capped letters
            End Select
        End If
    Next i

Code Ends - - -

Also dont forget to declare the X variable. (Dim x as integer) With the other declarations. You dont need to but it is good practice to explicitly declare all variables in your code.

When you call the function set the precision arg to 1. The Do Loop will run to check previous characters for a full stop. If any other characters other than full stop or space are found then function returns a true (Caps Found) otherwise just ignores and continues. Note that this is only checking for Full Stops! with a little modofication you could check for other scentence ending chars such as ! and ?.

Glad I could help, Everyone here is really helpful and I have learned loads from this forum. Soon be an Expert eh!;)

Hi Jon,

I tried to modify the code to include other chars like ! and ? but I kept getting errors.. would you be able to help me..

I found out that ? unicode is 63 and ! unicode is 33 but couldnt manage to get it working..

any ideas?

What Errors are you getting? Where are the errors occurring, i.e. Where is the code stopping?

Jon

Hi,

Replace the Loop part of your code with this. All i have done is added some Ands and ORs to search for the punctuation chars you specified.

- Code Starts -

                Do While x > 1 And Asc(Mid(str, x, 1)) <> 46 _
                                        And Asc(Mid(str, x, 1)) <> 33 _
                                        And Asc(Mid(str, x, 1)) <> 63
                    x = x - 1
                    If Asc(Mid(str, x, 1)) = 32 Then 'Space so decrement the X variable
                        x = x - 1
                    ElseIf Asc(Mid(str, x, 1)) = 46 Or _
                              Asc(Mid(str, x, 1)) = 33 Or _
                              Asc(Mid(str, x, 1)) = 63 Then
                        Exit Do
                    Else
                        FindUpper = True
                        Exit Function
                    End If
                Loop

- Code Ends -

This works for me. Post back if you have problems though.

Jon

that worked great..

I tried changing that again to add some more as I got the idea, as you added Asc(Mid(str, x, 1)) = 73 Or_ I thought I could add a few more of those in, but the next line would go red and error expect Then. Can you only have two ORs before a THEN ??

I wanted to add :

Asc(Mid(str, x, 1)) = 73 Or_ to get rid of I
Asc(Mid(str, x, 1)) = 58 Or_ to get rid of :
Asc(Mid(str, x, 1)) = 45 Or_ to get rid of -

sorry.. me being silly..

I didnt have a space between Or and _

Thanks