Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
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
 
Old March 14th, 2005, 04:01 AM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 100
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

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])'.

??
 
Old March 14th, 2005, 09:45 AM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 100
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old March 14th, 2005, 12:25 PM
Authorized User
 
Join Date: Feb 2005
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default

great that works for WholeWordOnly.. what about letter beginning with a capital?
 
Old March 14th, 2005, 01:22 PM
Authorized User
 
Join Date: Feb 2005
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
:)
 
Old May 18th, 2005, 06:50 AM
Authorized User
 
Join Date: Feb 2005
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old May 18th, 2005, 06:52 AM
Authorized User
 
Join Date: Feb 2005
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old May 18th, 2005, 08:29 AM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 100
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


 
Old May 19th, 2005, 05:58 AM
Authorized User
 
Join Date: Feb 2005
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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..

 
Old May 19th, 2005, 06:28 AM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 100
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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







Similar Threads
Thread Thread Starter Forum Replies Last Post
Can I open access DB stored in CD from VB Andraw Pro VB Databases 1 August 14th, 2008 10:05 AM
How to connect to Access db thru VB.NET? am_kuthus Visual Studio 2008 1 June 3rd, 2008 04:48 AM
Connecting to Access 20007 DB with vb.net JohnBoy VB.NET 1 May 21st, 2008 01:26 PM
CAPTION OF ACCESS DB IN VB akash VB How-To 5 March 8th, 2007 11:01 PM
New in .Net VB but learning datareader, access DB tjgrindsted ASP.NET 2.0 Basics 2 February 6th, 2007 08:49 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.