Solved

StrConv() not working

Posted on 2010-09-10
43
1,011 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 18
  • 15
  • 4
  • +2
43 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33647128
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
ID: 33647198
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
ID: 33647210
That is what I am suggesting, yes.
0
Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

 
LVL 1

Author Comment

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

Author Comment

by:currentdb
ID: 33647272
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
ID: 33647287
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33647370
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
ID: 33647390
>>there is no StrConvert()  it is StrConv()

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

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33647428
get some coffee ;-)
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33647512
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
ID: 33647554
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
ID: 33647592
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
ID: 33647681
Is the desk ok?
0
 
LVL 2

Expert Comment

by:DuBoisCG
ID: 33647689
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
ID: 33647793
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33647827
currentdb,

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

Expert Comment

by:DuBoisCG
ID: 33647911
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33648087
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
ID: 33648514
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33648609
<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
ID: 33648666
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33648696
that is correct...
0
 
LVL 1

Author Comment

by:currentdb
ID: 33648715
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33648725
do a debug>compile first, and make sure there are no errors raised before testing the application.
0
 
LVL 1

Author Comment

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

Expert Comment

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

Author Comment

by:currentdb
ID: 33650043
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
ID: 33650754
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
ID: 33650769
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33650821

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
ID: 33661819
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33661893
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33661906
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
ID: 33662019
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
ID: 33662140
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33662163

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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33662202
<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
ID: 33662347
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33662421
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
ID: 33662720
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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 400 total points
ID: 33663003
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
ID: 33663199
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
ID: 33665259
Thank you so much for your help. Hope to see you around.

Cheers!
currentdb
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

739 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