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

One More thing.

Because you are using the Asc function multiple times it would be best to assign the value of the function to a variable and use the variable for your comparisons. In fact, I always try to assign functions to variables as code will execute much quicker like this.

Something like -

'Assign value of ASC function to a variable
lngASCLetter = Asc(Mid(str, x, 1))

'We then use the variable rather than running the ASC function again.
Do While x > 1 And strLpLetter <> 46 _
    And lngASCLetter <> 33 _
    And lngASCLetter <> 63
    etc ...

Hope this helps,

Jon
 
Old March 8th, 2005, 10:24 AM
Authorized User
 
Join Date: Feb 2005
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Its strange because it still seems to pick up messages like :

I wonder what would have happened if the scenes were shown in a different order - would our understanding of the story have been different? I think it probably would have been.

I thought the above message would not be pulled out because we have included ?

and

Hello I am back same for this one.. I has been included

and

but through a series of conventions that make a story and make it seem like NEWS This picks out a full word in capitals, when I was only looking for the first word to be a capital

and

But that is a bit too complex, the truth is when we 'narratte' something or tell it then we are telling a story ERGO: A narrative is a story. I had included : as well

code im using is :

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, !, I, :, -, 63
    Do While x > 1 And Asc(Mid(str, x, 1)) <> 46 _
                                        And Asc(Mid(str, x, 1)) <> 33 _
                                        And Asc(Mid(str, x, 1)) <> 73 _
                                        And Asc(Mid(str, x, 1)) <> 58 _
                                        And Asc(Mid(str, x, 1)) <> 45 _
                                        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)) = 73 Or _
                              Asc(Mid(str, x, 1)) = 58 Or _
                              Asc(Mid(str, x, 1)) = 45 Or _
                              Asc(Mid(str, x, 1)) = 63 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


with the following query :

SELECT Caps([Message]) AS HasUpper, User, Message
FROM [Table]
WHERE (((Caps([Message]))=True));


am i missing something?
 
Old March 8th, 2005, 10:28 AM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 100
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The function is picking up the 'I' s as capitals. We need to add another part that will not pick up these as capital words. I will have a look and get back to you shortly.

Jon
 
Old March 8th, 2005, 10:34 AM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 100
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Ok,

All we need to do is add some more ands and Ors to handle the 'I's

i.e.

    Do While x > 1 And Asc(Mid(str, x, 1)) <> 46 _
                   And Asc(Mid(str, x, 1)) <> 33 _
                   And Asc(Mid(str, x, 1)) <> 73 _
                   And Asc(Mid(str, x, 1)) <> 58 _
                   And Asc(Mid(str, x, 1)) <> 45 _
                   And Asc(Mid(str, x, 1)) <> 63 _
                   And Asc(Mid(str, x, 1)) <> asc("I")

Does this help?

Jon
 
Old March 8th, 2005, 10:53 AM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 100
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Ok,

Ignore my last post! I had and early start today!!!

You need to change 2 lines in the function

    If intCountUppers >= Precision Then

change to

    If intCountUppers > Precision Then

This will then work. But. Any capped words such as ERGO meet the requirements of the function and will make the function return true. Do you want to stop picking up Capped words and just find words that start in a caps where punctation was not used before the word?
 
Old March 8th, 2005, 11:04 AM
Authorized User
 
Join Date: Feb 2005
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I used the function FindUpper as a seperate query to get the capital words within the sentences.. ideally in this one I only want it to pick out the messages which have words which begin with a capital..
 
Old March 8th, 2005, 11:20 AM
Authorized User
 
Join Date: Feb 2005
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I found by changing the two lines to : If intCountUppers > Precision
stopped pulling out the messages which had words starting with a capital.. eg i had a message with some names being mentioned and that stopped being pulled out.. when i changed it back to >= they started being retrieved again ?

would it be easier if i perhaps email my db to you to look at the data.. that might help
 
Old March 8th, 2005, 11:27 AM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 100
Thanks: 0
Thanked 0 Times in 0 Posts
Default

No, Dont send it.

I am just trying to find a way to just pick out capped letters rather than words using one generic function (So that you can use it to find CAPPED words in one query but in another just find Capped letters. I have all the info I need i just need to adjust the function to meet both requirements. I will post back soon.

Jon
 
Old March 11th, 2005, 05:14 AM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 100
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi akb786

Is this the sort of output you wanted form the function?

Message HasUpperWord HasUpperLetter
A man walked into the bar FALSE FALSE
A MAN walked into the bar TRUE TRUE
A man walked into the bar and wanted a beer FALSE FALSE
A man walked into the bar. He WANTED a beer TRUE TRUE
A man walked into the bar. He wanted a beer FALSE FALSE
A man walked into the bar He wanted a beer FALSE TRUE

Jon
 
Old March 13th, 2005, 06:59 PM
Authorized User
 
Join Date: Feb 2005
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Jon,

Similar to that.. yes. (if what I want isnt possible then what you suggested would be fine too)

I would prefer it if words with full capital letter ie, a word with more that 2 consecutive capitals to not be included in HasUpperLetter, so I would want 'A man walked into the bar. He WANTED a beer' to be retrieved with HasUpperWord - because of WANTED. But not part of HasUpperLetter. The main reason for having the HasUpperLetter function is to query on people talking about Names and places.. which begin with a capital.. so bringing out words with capitals doesnt fit in that category. Like i said if it can be done with the capital words as well then so be it but the ideal is for capital words to be retrieved with HasUpperWord and only words beginning with a capital in HasUpperLetter.. but also bearing in mind that 'I' should not be classed as a capital word or as a word with starting with a capital..

Hope this makes sense
-x-





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.