Solved

StrConv() not working

Posted on 2010-09-10
43
941 Views
Last Modified: 2013-11-27
Hi all,

I'm trying to change CAPITAL fields to normal fields where the first letter of each word is CAP where the rest is lower case. I have these fields NAME, ADDRESS, STATE, CITY.

NAME represents company name and instead of HOME BASE INC it should be Home Base Inc
ADDRESS represents the address and it should look like 12A  Barrett Street not (12 A   BARRETT STREET)
STATE should be New York, not NEW YORK
CITY same as the STATE field

I added these lines by using the vbProperCase function, but nothing is working. Still need to figure how I can make it work for other fields as well:
x = StrConv(rst![CITY], vbProperCase)
x = StrConv(rst![STATE], vbProperCase)

The whole code starts with a button on a form, loops through a table and exports specified fields into a Word merge document.

Any help would be appreciated.
Thanks.

currentdb
Private Sub Merge_Click()

   Dim Sql As String

   Dim pathFichierWord As String

   Dim Rs As DAO.Recordset

   Dim db As DAO.Database

   Set db = CurrentDb()

   Dim WordObj As Word.Application

   Dim docs As Word.Documents

   Dim doc As Word.Document

   Dim pb As New Form_frmProgBar

   Dim intK As Integer 

   Dim lngRecCount as long

   Dim x as String



   Dim path As String

   path = CurrentProject.path

   pathFichierWord = path + "\" + "IN\" + "letter2.docx"

   Sql = "SELECT * FROM tableM"

   Set Rs = db.OpenRecordset(Sql)



    On Error Resume Next

    Set WordObj = GetObject(, "Word.Application") 'New Word.Application

    If Err.Number <> 0 Then

       Set WordObj = CreateObject("Word.Application")

    End If

    WordObj.Visible = False

    

    rs.movelast

    lngRecCount = rs.RecordCount

    rs.movefirst



    pb.SetMessage "Upload to Word in progress...please wait"

    pb.SetCaption "Upload in progress"

    pb.SetBarVisible True



    While not rs.eof

        pb.SetBarPercent (rs.absoluteposition+1 )/ lngRecCount * 100

        x = StrConv(rst![CITY], vbProperCase)

        x = StrConv(rst![STATE], vbProperCase)



 

        Wait (2)

    

        WordObj.Documents.Add Template:=pathFichierWord, newtemplate:=False

        With WordObj.Selection

            NAME = Rs("NAME")

            ADDRESS = Rs("ADDRESS")

            CITY = Rs("CITY")

            STATE = Rs("STATE")

            ZIP_CODE = Rs("ZIP_CODE")

            NO_FILE = Rs("NO_FILE")

            AGENT = Rs("AGENT")

            NO_PHONE_AGENT = Rs("NO_PHONE_AGENT")

             

            WordObj.Selection.Goto what:=wdGoToBookmark, Name:="NAME"

            WordObj.Selection.TypeText NAME

                

            WordObj.Selection.Goto what:=wdGoToBookmark, Name:="ADDRESS"

            WordObj.Selection.TypeText ADDRESS

                

            WordObj.Selection.Goto what:=wdGoToBookmark, Name:="CITY"

            WordObj.Selection.TypeText CITY

                

            WordObj.Selection.Goto what:=wdGoToBookmark, Name:="STATE"

            WordObj.Selection.TypeText STATE

                

            WordObj.Selection.Goto what:=wdGoToBookmark, Name:="ZIP_CODE"

            WordObj.Selection.TypeText ZIP_CODE

                

            WordObj.Selection.Goto what:=wdGoToBookmark, Name:="NO_FILE"

            WordObj.Selection.TypeText NO_FILE

                

            WordObj.Selection.Goto what:=wdGoToBookmark, Name:="AGENT"

            WordObj.Selection.TypeText AGENT

                

            WordObj.Selection.Goto what:=wdGoToBookmark, Name:="NO_PHONE_AGENT"

            WordObj.Selection.TypeText NO_PHONE_AGENT

                

            WordObj.ActiveDocument.SaveAs path + "\" + "Output\" + NO_FILE + ".docx"

            WordObj.ActiveDocument.close

                  

        End With



        Rs.MoveNext

    Wend

          

    Rs.Close

    Set Rs = Nothing

          

    Set WordObj = Nothing

     

    pb.SetBarPercent (100)

    pb.SetMessage "Done!"



    Wait (5)

    Set pb = Nothing

   

End Sub

Open in new window

0
Comment
Question by:currentdb
  • 18
  • 15
  • 4
  • +2
43 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
All that the lines:

x = StrConv(rst![CITY], vbProperCase)
x = StrConv(rst![STATE], vbProperCase)


are doing is assigning a value to the variable x--which you then never actually use.  Indeed, the second line overwrites the first :)

Try instead converting:

            NAME = Rs("NAME")
            ADDRESS = Rs("ADDRESS")
            CITY = Rs("CITY")
            STATE = Rs("STATE")
            ZIP_CODE = Rs("ZIP_CODE")
            NO_FILE = Rs("NO_FILE")
            AGENT = Rs("AGENT")
            NO_PHONE_AGENT = Rs("NO_PHONE_AGENT")


to:

            StrConvert(NAME = Rs("NAME"), vbProperCase)
            StrConvert(ADDRESS = Rs("ADDRESS"), vbProperCase)

et cetera
0
 
LVL 1

Author Comment

by:currentdb
Comment Utility
Hi mattewspatrick,

thanks for your quick reply to my question.

That means I have to change the code here & replace these 4 fields (no need to make it for all of them):
With WordObj.Selection
            NAME = Rs("NAME")
            ADDRESS = Rs("ADDRESS")
            CITY = Rs("CITY")
            STATE = Rs("STATE")
            ZIP_CODE = Rs("ZIP_CODE")
            NO_FILE = Rs("NO_FILE")
            AGENT = Rs("AGENT")
            NO_PHONE_AGENT = Rs("NO_PHONE_AGENT")

with

With WordObj.Selection
            StrConvert(NAME = Rs("NAME"), vbProperCase)
            StrConvert(ADDRESS = Rs("ADDRESS"), vbProperCase)
            StrConvert(CITY = Rs("CITY"), vbProperCase)
            StrConvert(STATE = Rs("STATE"), vbProperCase)
            ZIP_CODE = Rs("ZIP_CODE")
            NO_FILE = Rs("NO_FILE")
            AGENT = Rs("AGENT")
            NO_PHONE_AGENT = Rs("NO_PHONE_AGENT")

Is that ok ?

Thanks
0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
That is what I am suggesting, yes.
0
 
LVL 1

Author Comment

by:currentdb
Comment Utility
ok I'm going to try and hope it will work.
0
 
LVL 1

Author Comment

by:currentdb
Comment Utility
I'm back.
It does not work. The line for the first field (name) remained in red (same thing for the 3 other fields).

I changed  StrConvert(NAME = Rs("NAME"), vbProperCase)
for
StrConv(NAME = Rs("NAME"), vbProperCase)

And I have a compile error: ''Expected :=''

That would mean put something before the StrConv...
0
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 100 total points
Comment Utility
Sorry, had some typos there.  Should have been:


            NAME = StrConvert(Rs("NAME"), vbProperCase)
            ADDRESS = StrConvert(Rs("ADDRESS"), vbProperCase)
            CITY = StrConvert(Rs("CITY"), vbProperCase)
            STATE = StrConvert(Rs("STATE"), vbProperCase)
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
there is no StrConvert()  it is StrConv()


            NAME = StrConv(Rs("NAME"), vbProperCase)
            ADDRESS = StrConv(Rs("ADDRESS"), vbProperCase)
            CITY = StrConv(Rs("CITY"), vbProperCase)
            STATE = StrConv(Rs("STATE"), vbProperCase)
0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
>>there is no StrConvert()  it is StrConv()

<Patrick bangs his head on the desk>
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
get some coffee ;-)
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
btw,
you can run an update query to set the fields in Proper case format, so you don't need to do the conversion everytime.
0
 
LVL 1

Author Comment

by:currentdb
Comment Utility
LOL, I knew it was no StrConvert because got a compile error. Replaced it with StrConv().

The weird thing is that some addresses have the P.O BOX....and applying the StrConv() function, I have P.o Box.

Is there some exception to this ?

Thanks
currentdb
0
 
LVL 1

Author Comment

by:currentdb
Comment Utility
Hi Capricorn,

Running an update query would be fine, but the problem is that the data is extracted from a bigger database and the data from this bigger database is extracted from a mainframe.

I guess that maybe the StrConv() does not need to be inserted in Access. Because data comes fom SAS EG (SAS Enterprise Guide), some conversion can be made there, but  I have to find another way because the SQL code that SAS EG uses is a little different from Access. I did some search and there was no StrConv() function that can be used...sigh.
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
Is the desk ok?
0
 
LVL 2

Expert Comment

by:DuBoisCG
Comment Utility
You need to code around it by using the instr() function to look for exceptions... BTW: If you do this, you might as well write a function to look for/correct common things like "PO Box" "P.O. Box" or even your very own typo (above) "P.O Box".  A long time ago we wrote a program that corrected all of these address anomolies for a customers Goldmine database... I'll see if I can dig up the code.
0
 
LVL 1

Author Comment

by:currentdb
Comment Utility
DuBoisCG,
Just took a look through almost 2120 records. I have ''PO BOX'', ''P.O BOX'', ''P.O. BOX'' .
The StrConv() function will change them to ''P.o Box'', ''P.o. Box''.
A small check on the postal office web site and I saw that the correct spelling is still ''P.O. BOX''

For now the StrConv()  converts addresses like ''12 BLOOD STREET'' TO ''12 Blood Street''. But if there is ''P.O. BOX'', it has to remain the same, although there are so many spelling mistakes :(
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
currentdb,

you will then need a user define function to achieve that.
0
 
LVL 2

Expert Comment

by:DuBoisCG
Comment Utility
I understand.  My code (it's been 8 years, but the theory is still sound) was like this:
If instr(1, ucase(sAddress), "P.O") = 0 then
   sAddress = StrConv(sAddress, vbProperCase)
else
   sAddress = CorrectAddress(sAddress)
EndIf

Now this "CorrectAddress" function did several things (yours could simply standardize the P.O. Box part of the address and shouldn't take long to write).  Ours went a step further and stripped all non-alphanumeric characters (except spaces and commas), went back in and replaced the "." in the PO Box parts, removed double spaces, etc..

Hope this helps.
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
see this link

http://allfaq.org/forums/t/99149.aspx

the code is posted below
Public Function ProperCase(AnyText As String) As String

    'Convert passed text to all lowercase. Use ProperCase() as you would a built-in Access function.

    'If passed value is a null, ignore all the stuff below.

   

    If IsNull(Nz(AnyText, Null)) Then GoTo Exit_ProperCase

       

    Dim intCounter As Integer, OneChar As String

   

    'First convert to initial cap, followed by all lowercase.

    AnyText = UCase(Left(AnyText, 1)) & LCase(mid(AnyText, 2))

    'Look at each character, starting at the second character.

    For intCounter = 2 To Len(AnyText)

        OneChar = mid(AnyText, intCounter, 1)

        'If current character (OneChar) is a space or hyphen...

        Select Case OneChar

            Case "-", "/", ".", "'", "&"

                '...convert the character after space/hyphen/slash/period/apostrophe/ampersand to uppercase.

                ' Such as A.B.C. Industries, Sharron O'Conner, B&B Mfg

                AnyText = Left(AnyText, intCounter) & UCase(mid(AnyText, intCounter + 1, 1)) & mid(AnyText, intCounter + 2, 255)

           

            Case "c"

                ' Take care of the McAfee's, McDonalds & McLaughlins and such

                If mid(AnyText, intCounter - 1, 1) = "M" Then

                    AnyText = Left(AnyText, intCounter) & UCase(mid(AnyText, intCounter + 1, 1)) & mid(AnyText, intCounter + 2, 255)

                End If

   

            Case " "

                Select Case mid(AnyText, intCounter + 1, 2)

                    Case "de"

                        'Add any other exceptions here Example: Oscar de La Hoya

                        If mid(AnyText, intCounter + 3, 1) = " " Then

                        

                            AnyText = Left(AnyText, intCounter) & LCase(mid(AnyText, intCounter + 1, 1)) & mid(AnyText, intCounter + 2, 255)

                        Else

                            AnyText = Left(AnyText, intCounter) & UCase(mid(AnyText, intCounter + 1, 1)) & mid(AnyText, intCounter + 2, 255)

 

                        End If

                    Case Else

                        ' Example: A B C Manufacturing

                        AnyText = Left(AnyText, intCounter) & UCase(mid(AnyText, intCounter + 1, 1)) & mid(AnyText, intCounter + 2, 255)

   

                End Select

        End Select

    Next

   

    'All done, return current contents of AnyText variable.

    ProperCase = AnyText

   

Exit_ProperCase:

   

End Function

Open in new window

0
 
LVL 1

Author Comment

by:currentdb
Comment Utility
Capricorn,

From the code you submitted, it would mean create a module and call this module from the Private Sub Merge_Click() button, right ? In this case, in this function there will be only the field ADDRESS while the others that use the StrConv() function will be left in the OnClick procedure (Merge_Click() button) ? Am confused there.

DuBoisCG
If using the procedure as you described, that means having  a lot of If/Else or that will be just for the ADDRESS field while the other 3 remain the same?

That would look like:
With WordObj.Selection
   If instr(1, ucase(ADDRESS), "P.O. BOX ") = 0 then
      sAddress = StrConv(sAddress, vbProperCase)
   else
      sAddress = CorrectAddress(sAddress)
   EndIf
   NAME = StrConv(Rs("NAME"), vbProperCase)
   CITY = StrConv(Rs("CITY"), vbProperCase)
   STATE = StrConv(Rs("STATE"), vbProperCase)
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
<create a module and call this module from the Private Sub Merge_Click() button, right ?>

no, you wil not call the module, you will call the function
ProperCase(AnyText As String) ' passing the name of the field to the function, in place of

"AnyText As String"

Address=ProperCase(Rs("Address"))

0
 
LVL 1

Author Comment

by:currentdb
Comment Utility
Capricorn,

Wrong spelling for module. I mean the function :o)

I'm not sure if I have to call the function after the line 'With WordObj.Selection' or maybe I'm wrong.

With WordObj.Selection
            NAME = StrConv(Rs("NAME"), vbProperCase)
            Call ProperCase(ADDRESS As String)
            'ADDRESS = StrConv(Rs("ADDRESS"), vbProperCase)
            ADDRESS=ProperCase(Rs("ADDRESS"))
            CITY = StrConv(Rs("CITY"), vbProperCase)
            STATE = StrConv(Rs("STATE"), vbProperCase)
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
that is correct...
0
 
LVL 1

Author Comment

by:currentdb
Comment Utility
Thanks. I'm going to give it a try and hope that the database won't crash. I'll be back in a few mins.
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
do a debug>compile first, and make sure there are no errors raised before testing the application.
0
 
LVL 1

Author Comment

by:currentdb
Comment Utility
That's what I'm going to do + make a backup just in case...
0
 
LVL 2

Expert Comment

by:DuBoisCG
Comment Utility
Make sure to acceot Capricorns solution... really this should have been two questions.
0
 
LVL 1

Author Comment

by:currentdb
Comment Utility
Capricorn,

Didn't compile it yet...
As the code has many choices, one has to remain and the other options have to be commented out.

Because in the database some records do contain the ''P.O. BOX'' reference, then we need to make the change only for records that are spelled like ''PO BOX'' or ''P.O BOX'', then the line I have to change would be like this one:

Case "PO BOX"
                ' Take care of the PO BOX and P.O BOX
                If mid(ADDRESS, intCounter - 1, 1) = "PO BOX" Then
                    ADDRESS = Left(ADDRESS, intCounter) & UCase(mid(ADDRESS, intCounter + 1, 1)) & mid(ADDRES, intCounter + 2, 255)
                Else
                   If If mid(ADDRESS, intCounter - 1, 1) = "P.O BOX" Then
                    ADDRESS = Left(ADDRESS, intCounter) & UCase(mid(ADDRESS, intCounter + 1, 1)) & mid(ADDRES, intCounter + 2, 255)
                End If

But something's wrong and it's really quite a time I did not use the intCounter function :(
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
mid(ADDRESS, intCounter - 1, 1) = "PO BOX"  as well as the Else expression can never be true.  The left side expression will always yield a string one character long.
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
mid(ADDRESS, intCounter - 1, 1) = "PO BOX"  as well as the Else expression can never be true.  
--------------------------------------^-- The left side expression will always yield a string one character long.
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility

for Case "PO BOX"

correct it first at the start of the function

   If IsNull(Nz(AnyText, Null)) Then GoTo Exit_ProperCase
   If AnyText="PO BOX" then AnyText=Replace(AnyText,"PO","P.O.")
0
 
LVL 1

Author Comment

by:currentdb
Comment Utility
Hi Capricorn & Grayl,

I'm back. I had to upgrade my computer and hence, I was not able to access the net.

I created the ProperCase module and inserted the code as it is below. Then I did a debug>compile first. The only error I have is back to the form where I have to call the function ProperCase.

The line which is highlighted in red (not in yellow) is Call ProperCase(ADDRESS As String). The error I have is ''list separator or ) ''

I changed the line for Call ProperCase, but the line was still in red and the error is now ''Expected variable or procedure, not module''

Then I declared the variable as Dim ADDRESS As String but the problem is still the same.

Public Function ProperCase(AnyText As String) As String

    'Convert passed text to all lowercase. Use ProperCase() as you would a built-in Access function.

    'If passed value is a null, ignore all the stuff below.

   

    If IsNull(Nz(ADDRESS, Null)) Then GoTo Exit_ProperCase

    If ADDRESS="PO BOX" then ADDRESS=Replace(ADDRESS,"PO","P.O.")

       

    Dim intCounter As Integer, OneChar As String

   

    'First convert to initial cap, followed by all lowercase.

    ADDRESS = UCase(Left(ADDRESS, 1)) & LCase(mid(ADDRESS, 2))

    'Look at each character, starting at the second character.

    For intCounter = 2 To Len(ADDRESS)

        OneChar = mid(ADDRESS, intCounter, 1)

        'If current character (OneChar) is a space or hyphen...

        Select Case OneChar

            Case "-", "/", ".", "'", "&"

                '...convert the character after space/hyphen/slash/period/apostrophe/ampersand to uppercase.

                ' Such as A.B.C. Industries, Sharron O'Conner, B&B Mfg

                ADDRESS = Left(ADDRESS, intCounter) & UCase(mid(ADDRESS, intCounter + 1, 1)) & mid(ADDRESS, intCounter + 2, 255)

           

            'Case "PO BOX"

                ' Take care of the McAfee's, McDonalds & McLaughlins and such

            '    If mid(ADDRESS, intCounter - 1, 1) = "PO BOX" Then

            '        ADDRESS = Left(ADDRESS, intCounter) & UCase(mid(ADDRESS, intCounter + 1, 1)) & mid(ADDRESS, intCounter + 2, 255)

             '   End If

   

            'Case " "

            '    Select Case mid(ADDRESS, intCounter + 1, 2)

            '        Case "de"

                        'Add any other exceptions here Example: Oscar de La Hoya

            '            If mid(ADDRESS, intCounter + 3, 1) = " " Then

                        

             '               ADDRESS = Left(ADDRESS, intCounter) & LCase(mid(ADDRESS, intCounter + 1, 1)) & mid(ADDRESS, intCounter + 2, 255)

              '          Else

              '              ADDRESS = Left(ADDRESS, intCounter) & UCase(mid(ADDRESS, intCounter + 1, 1)) & mid(ADDRESS, intCounter + 2, 255)

 

               '         End If

               '     Case Else

                        ' Example: A B C Manufacturing

               '         ADDRESS = Left(ADDRESS, intCounter) & UCase(mid(ADDRESS, intCounter + 1, 1)) & mid(ADDRESS, intCounter + 2, 255)

   

                'End Select

        End Select

    Next

   

    'All done, return current contents of ADDRESS variable.

    ProperCase = ADDRESS

   

Exit_ProperCase:

   

End Function

Open in new window

0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
this not correct < Call ProperCase(ADDRESS As String) >

use the function like this

Address=ProperCase(rs("Address"))

and you DO NOT need to alter the Codes In the FUNCTION like you did


DELETE the function that you revised

use the codes below
Public Function ProperCase(AnyText As String) As String

    'Convert passed text to all lowercase. Use ProperCase() as you would a built-in Access function.

    'If passed value is a null, ignore all the stuff below.

   

    If IsNull(Nz(AnyText, Null)) Then GoTo Exit_ProperCase

    If AnyText="PO BOX" then AnyText=Replace(AnyText,"PO","P.O.")

   

    Dim intCounter As Integer, OneChar As String

   

    'First convert to initial cap, followed by all lowercase.

    AnyText = UCase(Left(AnyText, 1)) & LCase(mid(AnyText, 2))

    'Look at each character, starting at the second character.

    For intCounter = 2 To Len(AnyText)

        OneChar = mid(AnyText, intCounter, 1)

        'If current character (OneChar) is a space or hyphen...

        Select Case OneChar

            Case "-", "/", ".", "'", "&"

                '...convert the character after space/hyphen/slash/period/apostrophe/ampersand to uppercase.

                ' Such as A.B.C. Industries, Sharron O'Conner, B&B Mfg

                AnyText = Left(AnyText, intCounter) & UCase(mid(AnyText, intCounter + 1, 1)) & mid(AnyText, intCounter + 2, 255)

           

            Case "c"

                ' Take care of the McAfee's, McDonalds & McLaughlins and such

                If mid(AnyText, intCounter - 1, 1) = "M" Then

                    AnyText = Left(AnyText, intCounter) & UCase(mid(AnyText, intCounter + 1, 1)) & mid(AnyText, intCounter + 2, 255)

                End If

   

            Case " "

                Select Case mid(AnyText, intCounter + 1, 2)

                    Case "de"

                        'Add any other exceptions here Example: Oscar de La Hoya

                        If mid(AnyText, intCounter + 3, 1) = " " Then

                        

                            AnyText = Left(AnyText, intCounter) & LCase(mid(AnyText, intCounter + 1, 1)) & mid(AnyText, intCounter + 2, 255)

                        Else

                            AnyText = Left(AnyText, intCounter) & UCase(mid(AnyText, intCounter + 1, 1)) & mid(AnyText, intCounter + 2, 255)

 

                        End If

                    Case Else

                        ' Example: A B C Manufacturing

                        AnyText = Left(AnyText, intCounter) & UCase(mid(AnyText, intCounter + 1, 1)) & mid(AnyText, intCounter + 2, 255)

   

                End Select

        End Select

    Next

   

    'All done, return current contents of AnyText variable.

    ProperCase = AnyText

   

Exit_ProperCase:

   

End Function

Open in new window

0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
post sample data from field address with PO BOX  in the field
post all combinations of  PO BOX format.  
0
 
LVL 1

Author Comment

by:currentdb
Comment Utility
The combinaisons with the PO BOX format are already in the module:

If IsNull(Nz(ADDRESS, Null)) Then GoTo Exit_ProperCase
If ADDRESS="PO BOX" then ADDRESS=Replace(ADDRESS,"PO","P.O.")
If ADDRESS="P.O BOX" then ADDRESS=Replace(ADDRESS,"P.O","P.O.")

Actually the table contains:
P.O BOX
PO BOX
P.O. BOX

The last one ''P.O. BOX'' is correct while records with ''P.O BOX'' and PO BOX'' are incorrect.

A data sample of the ADDRESS field:
PO BOX 247
P.O BOX 189
P.O. BOX 47

Other records contain normal addresses (no PO BOX addresses).

Let me know if you need other info.

Thanks.
0
 
LVL 1

Author Comment

by:currentdb
Comment Utility
Capricorn,

I thought I had to change the AnyText with the field ADDRESS. Idid not understand that the way it was. I put the code in the module as it was in its original form.

Still after the compile I get the message at the same line (ADDRESS=ProperCase(rs("ADDRESS") Expected variable or procedure, not module. In this case, only the word ProperCase is highlighted (black).

Maybe the call procedure is not where it should be:

With WordObj.Selection
            NAME = StrConv(Rs("NAME"), vbProperCase)
            ADDRESS=ProperCase(rs("ADDRESS"))
            'ADDRESS = StrConv(Rs("ADDRESS"), vbProperCase)
            'ADDRESS=ProperCase(Rs("ADDRESS"))
            CITY = StrConv(Rs("CITY"), vbProperCase)
            STATE = StrConv(Rs("STATE"), vbProperCase)

0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility

DELETE the FUNCTION that you altered

use the codes below







Public Function ProperCase(AnyText As String) As String

    'Convert passed text to all lowercase. Use ProperCase() as you would a built-in Access function.

    'If passed value is a null, ignore all the stuff below.

   

    If IsNull(Nz(AnyText, Null)) Then GoTo Exit_ProperCase

    If Instr(AnyText,"PO BOX") then AnyText=Replace(AnyText,"PO BOX","P.O. BOX")

    If Instr(AnyText,"P.O BOX") then AnyText=Replace(AnyText,"P.O BOX","P.O. BOX")

   

    Dim intCounter As Integer, OneChar As String

   

    'First convert to initial cap, followed by all lowercase.

    AnyText = UCase(Left(AnyText, 1)) & LCase(mid(AnyText, 2))

    'Look at each character, starting at the second character.

    For intCounter = 2 To Len(AnyText)

        OneChar = mid(AnyText, intCounter, 1)

        'If current character (OneChar) is a space or hyphen...

        Select Case OneChar

            Case "-", "/", ".", "'", "&"

                '...convert the character after space/hyphen/slash/period/apostrophe/ampersand to uppercase.

                ' Such as A.B.C. Industries, Sharron O'Conner, B&B Mfg

                AnyText = Left(AnyText, intCounter) & UCase(mid(AnyText, intCounter + 1, 1)) & mid(AnyText, intCounter + 2, 255)

           

            Case "c"

                ' Take care of the McAfee's, McDonalds & McLaughlins and such

                If mid(AnyText, intCounter - 1, 1) = "M" Then

                    AnyText = Left(AnyText, intCounter) & UCase(mid(AnyText, intCounter + 1, 1)) & mid(AnyText, intCounter + 2, 255)

                End If

   

            Case " "

                Select Case mid(AnyText, intCounter + 1, 2)

                    Case "de"

                        'Add any other exceptions here Example: Oscar de La Hoya

                        If mid(AnyText, intCounter + 3, 1) = " " Then

                        

                            AnyText = Left(AnyText, intCounter) & LCase(mid(AnyText, intCounter + 1, 1)) & mid(AnyText, intCounter + 2, 255)

                        Else

                            AnyText = Left(AnyText, intCounter) & UCase(mid(AnyText, intCounter + 1, 1)) & mid(AnyText, intCounter + 2, 255)

 

                        End If

                    Case Else

                        ' Example: A B C Manufacturing

                        AnyText = Left(AnyText, intCounter) & UCase(mid(AnyText, intCounter + 1, 1)) & mid(AnyText, intCounter + 2, 255)

   

                End Select

        End Select

    Next

   

    'All done, return current contents of AnyText variable.

    ProperCase = AnyText

   

Exit_ProperCase:

   

End Function

Open in new window

0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
<Still after the compile I get the message at the same line (ADDRESS=ProperCase(rs("ADDRESS") Expected variable or procedure, not module. In this case, only the word ProperCase is highlighted (black). >


rename the module that you created to modProperCase  

DO NOT use the name of the function as name of a module..
0
 
LVL 1

Author Comment

by:currentdb
Comment Utility
Capricorn,

Module was renamed to modProperCase.

Now there is no any compile error.  Then I ran the entire code from the Private Sub Merge_Click() button on the form. It looks like the line ADDRESS = ProperCase(Rs("ADDRESS")) has no any effect because I still see records changed to ''Po Box'' instead of P.O. BOX

0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
i run  
ProperCase("PO BOX 123")
P.O. Box 123    ' < I got this

ProperCase("P.O BOX 123")
P.O. Box 123     ' < i Got this


and don't expect  BOX to be all Cap, that is not the Proper Case, it is Upper Case
0
 
LVL 1

Author Comment

by:currentdb
Comment Utility
BOX should remain all Cap. If that's not Proper Case, then we have to change the line ADDRESS = ProperCase(Rs("ADDRESS")) to Upper Case ? Changing to Upper Case would make BOX remain Cap...but the PO need to be P.O. That means we have to use a mix of ProperCase and Upper Case ?
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 400 total points
Comment Utility
use this codes and do not alter..




Public Function ProperCase(AnyText As String) As String

    'Convert passed text to all lowercase. Use ProperCase() as you would a built-in Access function.

    'If passed value is a null, ignore all the stuff below.

   

    If IsNull(Nz(AnyText, Null)) Then GoTo Exit_ProperCase

    If InStr(AnyText, "PO BOX") Then AnyText = Replace(AnyText, "PO BOX", "P.O. BOX"): ProperCase = AnyText: Exit Function

    If InStr(AnyText, "P.O BOX") Then AnyText = Replace(AnyText, "P.O BOX", "P.O. BOX"): ProperCase = AnyText: Exit Function

   

    Dim intCounter As Integer, OneChar As String

   

    'First convert to initial cap, followed by all lowercase.

    AnyText = UCase(Left(AnyText, 1)) & LCase(Mid(AnyText, 2))

    'Look at each character, starting at the second character.

    For intCounter = 2 To Len(AnyText)

        OneChar = Mid(AnyText, intCounter, 1)

        'If current character (OneChar) is a space or hyphen...

        Select Case OneChar

            Case "-", "/", ".", "'", "&"

                '...convert the character after space/hyphen/slash/period/apostrophe/ampersand to uppercase.

                ' Such as A.B.C. Industries, Sharron O'Conner, B&B Mfg

                AnyText = Left(AnyText, intCounter) & UCase(Mid(AnyText, intCounter + 1, 1)) & Mid(AnyText, intCounter + 2, 255)

           

            Case "c"

                ' Take care of the McAfee's, McDonalds & McLaughlins and such

                If Mid(AnyText, intCounter - 1, 1) = "M" Then

                    AnyText = Left(AnyText, intCounter) & UCase(Mid(AnyText, intCounter + 1, 1)) & Mid(AnyText, intCounter + 2, 255)

                End If

   

            Case " "

                Select Case Mid(AnyText, intCounter + 1, 2)

                    Case "de"

                        'Add any other exceptions here Example: Oscar de La Hoya

                        If Mid(AnyText, intCounter + 3, 1) = " " Then

                        

                            AnyText = Left(AnyText, intCounter) & LCase(Mid(AnyText, intCounter + 1, 1)) & Mid(AnyText, intCounter + 2, 255)

                        Else

                            AnyText = Left(AnyText, intCounter) & UCase(Mid(AnyText, intCounter + 1, 1)) & Mid(AnyText, intCounter + 2, 255)

 

                        End If

                    Case Else

                        ' Example: A B C Manufacturing

                        AnyText = Left(AnyText, intCounter) & UCase(Mid(AnyText, intCounter + 1, 1)) & Mid(AnyText, intCounter + 2, 255)

   

                End Select

        End Select

    Next

   

    'All done, return current contents of AnyText variable.

    ProperCase = AnyText

   

Exit_ProperCase:

   

End Function

Open in new window

0
 
LVL 1

Author Comment

by:currentdb
Comment Utility
Capricorn,

It now works as expected! :o)

Would you like to have all points? matthewspatrick also helped for the StrConv() function. I could split the points, give you more. Let me know.
0
 
LVL 1

Author Closing Comment

by:currentdb
Comment Utility
Thank you so much for your help. Hope to see you around.

Cheers!
currentdb
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now