Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old February 28th, 2005, 01:36 PM
Authorized User
 
Join Date: Feb 2005
Location: , , .
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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-
Reply With Quote
  #2 (permalink)  
Old March 2nd, 2005, 11:53 AM
Friend of Wrox
 
Join Date: Jan 2005
Location: , , United Kingdom.
Posts: 100
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #3 (permalink)  
Old March 3rd, 2005, 04:47 PM
Authorized User
 
Join Date: Feb 2005
Location: , , .
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #4 (permalink)  
Old March 3rd, 2005, 05:36 PM
Authorized User
 
Join Date: Feb 2005
Location: , , .
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?
Reply With Quote
  #5 (permalink)  
Old March 4th, 2005, 05:33 AM
Friend of Wrox
 
Join Date: Jan 2005
Location: , , United Kingdom.
Posts: 100
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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!;)
Reply With Quote
  #6 (permalink)  
Old March 8th, 2005, 09:41 AM
Authorized User
 
Join Date: Feb 2005
Location: , , .
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?
Reply With Quote
  #7 (permalink)  
Old March 8th, 2005, 09:44 AM
Friend of Wrox
 
Join Date: Jan 2005
Location: , , United Kingdom.
Posts: 100
Thanks: 0
Thanked 0 Times in 0 Posts
Default

What Errors are you getting? Where are the errors occurring, i.e. Where is the code stopping?

Jon

Reply With Quote
  #8 (permalink)  
Old March 8th, 2005, 09:51 AM
Friend of Wrox
 
Join Date: Jan 2005
Location: , , United Kingdom.
Posts: 100
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
  #9 (permalink)  
Old March 8th, 2005, 10:07 AM
Authorized User
 
Join Date: Feb 2005
Location: , , .
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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 -
Reply With Quote
  #10 (permalink)  
Old March 8th, 2005, 10:08 AM
Authorized User
 
Join Date: Feb 2005
Location: , , .
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default

sorry.. me being silly..

I didnt have a space between Or and _

Thanks
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 05:07 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.