Access DB using VB to select messages which contain Capitals

Hi All...

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.

I have attached a sample of the database and would really appreciate and help with this.. I have a deadline for a project so am really stuck.. 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-

ok maybe someone will be able to help with this instead..

I managed to get the above working.. but having a problem with something else, its similar in the sense that now what I would like is to have messages retrieved with words within a sentence that begin with a capital.. eg hi my name is John. It is basically to pull out when users are talking about names, places and things like that. Obviously I want to ignore capital letters after a full stop.. and have this to do so..

Function Caps(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

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

On Error Resume Next
If Precision = 0 Then Precision = 1

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 < 2 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
Caps = 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
Caps = True 'Just return a true and exit
Exit Function
End If
intCountUppers = 0
Case Else
intCountUppers = 0
End Select
End If
Next i
If intCountUppers >= Precision Then Caps = True Else Caps = False
End Function

Would someone be able to check this for me but also help me with that I want to add to it saying to ignore capitals after:

- (Unicode 45)
! (Unicode 33)
? (Unicode 63)

I dont know how to incorporate that..

could anyone help me with this please?

Thanks