 |
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA. |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|

February 28th, 2005, 01:36 PM
|
Authorized User
|
|
Join Date: Feb 2005
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Access DB using VB to select msgs with capitals
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-
|

March 2nd, 2005, 11:53 AM
|
Friend of Wrox
|
|
Join Date: Jan 2005
Posts: 100
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|

March 3rd, 2005, 04:47 PM
|
Authorized User
|
|
Join Date: Feb 2005
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|

March 3rd, 2005, 05:36 PM
|
Authorized User
|
|
Join Date: Feb 2005
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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?
|

March 4th, 2005, 05:33 AM
|
Friend of Wrox
|
|
Join Date: Jan 2005
Posts: 100
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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!;)
|

March 8th, 2005, 09:41 AM
|
Authorized User
|
|
Join Date: Feb 2005
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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?
|

March 8th, 2005, 09:44 AM
|
Friend of Wrox
|
|
Join Date: Jan 2005
Posts: 100
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
What Errors are you getting? Where are the errors occurring, i.e. Where is the code stopping?
Jon
|

March 8th, 2005, 09:51 AM
|
Friend of Wrox
|
|
Join Date: Jan 2005
Posts: 100
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|

March 8th, 2005, 10:07 AM
|
Authorized User
|
|
Join Date: Feb 2005
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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 -
|

March 8th, 2005, 10:08 AM
|
Authorized User
|
|
Join Date: Feb 2005
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
sorry.. me being silly..
I didnt have a space between Or and _
Thanks
|
|
 |