 |
| 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
|
|
|
|
March 14th, 2005, 04:01 AM
|
|
Friend of Wrox
|
|
Join Date: Jan 2005
Posts: 100
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Ok,
This seems to work, Test it thouroughly first though.
Function FindUppers(strText As String, WholeWordOnly As Boolean) As Boolean
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)
cLetterASC = Asc(cLetter)
Select Case cLetterASC
Case 65 To 90 'Cap Found
If WholeWordOnly Then
'And check thet 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)
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)
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
End Function
Good luck!
Jon
|
|
March 14th, 2005, 09:41 AM
|
|
Authorized User
|
|
Join Date: Feb 2005
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
sorry to be a pain.. but it doesnt seem to work :(
I have in the module - exactly what you said..
Function FindUppers(strText As String, WholeWordOnly As Boolean) As Boolean
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)
cLetterASC = Asc(cLetter)
Select Case cLetterASC
Case 65 To 90 'Cap Found
If WholeWordOnly Then
'And check thet 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)
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)
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
The query is :
SELECT FindUppers([Message]) AS WholeWordOnly, User, Message
FROM [Table]
WHERE (((FindUppers([Message]))=True));
but when i try to run that I get an error saying :
Wrong number of arguments used with function in query expression 'FindUppers([Message])'.
??
|
|
March 14th, 2005, 09:45 AM
|
|
Friend of Wrox
|
|
Join Date: Jan 2005
Posts: 100
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Thats OK,
What you need to do is supply 2 arguments for the function. The first is the text you need to evaluate and the second is the switch that says whether to find a whoel word or just an uppercase letter. e.g
SELECT FindUppers([Message],True) AS WholeWordOnly, User, Message
FROM [Table]
WHERE (((FindUppers([Message], True))=True));
Adding true tells the function to find whole words, adding false will tell the function to ignore whole words and only find words that have a capital letter.
Post back if you need further help.
Jon
|
|
March 14th, 2005, 12:25 PM
|
|
Authorized User
|
|
Join Date: Feb 2005
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
great that works for WholeWordOnly.. what about letter beginning with a capital?
|
|
March 14th, 2005, 01:22 PM
|
|
Authorized User
|
|
Join Date: Feb 2005
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
sorry i only just re-read my last post and realised that I didnt actually make any sense, im confusing myself now.
so lets try again.. yes that worked for the WholeWordOnly and i admit to not fully reading till the end of the post.. I thought that if I changed some of the True to False it may get somewhere.. and seemed to get it to work and then re-read your post. :)
Thank you so much for all your help.. you have been wonderful. I hope all this manages to help someone else out there with a similar problem.
Thanks again
:)
|
|
May 18th, 2005, 06:50 AM
|
|
Authorized User
|
|
Join Date: Feb 2005
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Hi Joe,
Urgent help required here please. If you remember you helped me retrieve messages within conversations which were fully written in capital letters and words with only had the first letter as a capital, this was done using a switch called WholeWordOnly and then called using SQL.
As I mentioned this query was part of a system which has data loaded and unloaded, I gave the system to the user and now she tried to load some data and then run this query, but is getting an error saying:
Run time error '5'
Invalid procedure call or argument
When I press debug to have a look at which line this is on it highlights:
cLetterASC = ASC(cLetter)
I dont understand why it works for some data sets and not others and I really need to sort this problem out and get it back to her.
The data that is being used this time is like has about 300+ records with lots of data in them..
Even when I try to use it from the query :
SELECT FindUppers([Message],True) AS WholeWordOnly, User, Message
FROM [Table]
WHERE (((FindUppers([Message],True))=True));
which calls the function i get the same error message..
:(
can you understand why this is happening?
I really need help with this please
|
|
May 18th, 2005, 06:52 AM
|
|
Authorized User
|
|
Join Date: Feb 2005
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
The full code for this function is:
Option Compare Database
Function FindUppers(strText As String, WholeWordOnly As Boolean) As Boolean
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)
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)
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)
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
End Function
|
|
May 18th, 2005, 08:29 AM
|
|
Friend of Wrox
|
|
Join Date: Jan 2005
Posts: 100
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Hi AKB,
Sorry to hear you are having trouble.
First, Compact and Repair the database
Second, Check your references in VBA.
If Microsoft ActiveX Data Objects 2.1 Library is above Microsoft DAO 3.6 Object Library then move DAO above Activex
Let us know if either of these resolve the problem
Jon
|
|
May 19th, 2005, 05:58 AM
|
|
Authorized User
|
|
Join Date: Feb 2005
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Hi Jon,
No neither of these worked.. :(
Any other ideas? I really cant understand why it doesnt accept some data sets and it does for others. Some are larger than others..
|
|
May 19th, 2005, 06:28 AM
|
|
Friend of Wrox
|
|
Join Date: Jan 2005
Posts: 100
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Ok,
Add an ErrTrap to the function to check the current value of cLetter variable on messages that produce an error. There must be something odd about the character that is being examined. Especially as it works fine for some messages and not others.
So -
Function FindUppers(....
On Error GoTo ErrTrap
'Code ...
'...
'...
'... End Code
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
Let us know what the results are
Jon
|
|
|
