Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Tidy up Function (MS Access 2003)

    I have a function that gets called after a user has been filling in a memo field. It goes off and looks for any keywords being in the Keywords table that a user has typed in as text. If it finds a word it capitalizes it. It works well except for the accuracy of a word. If the word Goal is found then theres no problem, however if it finds the word Goals it capitalizes the word as GOALs. I want it to ignore the word goals as its not a keyword. Any idea how to clean it up? Thanks

    Public Function CapKeyWords(ByVal Txt As String) As String
    Dim DB As DAO.Database, rst As DAO.Recordset

    Set DB = CurrentDb
    Set rst = DB.OpenRecordset("KeyWords", dbOpenDynaset)

    If Not rst.BOF Then
    Do
    Txt = Replace(Txt, rst!KeyWord, UCase(rst!KeyWord))
    rst.MoveNext
    Loop Until rst.EOF
    End If

    CapKeyWords = Txt

    Set rst = Nothing
    Set DB = Nothing

    End Function

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Tidy up Function (MS Access 2003)

    Well, you could change your Replace to this:

    Txt = Replace(Txt, " " & rst!KeyWord & " ", UCase(" " & rst!KeyWord & " "))

    This would look for each occurance of your keyword embedded between 2 blank spaces, essentially looking for " goal ". You would still have to handle the situations where your keyword was followed by a punctuation mark or if it was the first word in the string.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tidy up Function (MS Access 2003)

    Many thanks Mark, that fixes it perfectly. The word is between spaces so my problems cured. Many thanks, have a good weekend.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •