Creating Proper format on Address Direction

[wes9659]

Ok here is another one of those brain teaser for all the VBA Experts
Private Sub Address_AfterUpdate()
Address = StrConv(Address, vbProperCase)
End Sub

I have this code to put address test in proper format which it does with one exception the Directions are not right. For example

If I entered in
1234 ohio st. ne. I get 1234 Ohio St Ne. I want it to read 1234 Ohio St. NE.

My question is what would I do to allow only the exceptions of NE, SE, NW, SW, to be read in all uppercase. They always follow the address.

Once again thanks everyone for participating and helping in this form and helping others like me.

[orange]

You might want to look at US Postal Service Guidelines before doing anything.

[wes9659]

I understand how to address a letter: the issue was making the NE, SE, SW, NW remain capitalized after setting the rest of the address to proper case format. That is what I was asking about if I was not clear. Thank you

[June7]

This could get complicated.

Maybe:

Replace(Replace(Replace(Replace([Address], " Nw", " NW"), " Sw", " SW"), " Ne", " NE"), " Se", " SE")

Issue with that is if the address has something like: 319 Seaver St, the Se will become SE.

Otherwise, expect would need a VBA custom function.

Another option is to have a separate field for the direction.

[orange]

The USPS deals with Directionals in Addressing Guidelines here Note The preferred format, and the unacceptable.
Since you are automating addresses, you might want to use the preferred format. Just clarifying my comment.

[wes9659]

Thanks June,
Quick question how would I implement it?
I added it to next line in code and got error see attached photo. Here is what I was attempting to use unsuccessfully
Private Sub Address_AfterUpdate()
Address = StrConv(Address, vbProperCase)
Replace(Replace(Replace(Replace([Address], " Nw", " NW"), " Sw", " SW"), " Ne", " NE"), " Se", " SE")
End Sub

Thanks everyone for all the help and tremendous guidance.

[June7]

Address = Replace(Replace(Replace(Replace(StrConv(Address, vbProperCase), " Nw", " NW"), " Sw", " SW"), " Ne", " NE"), " Se", " SE")

Remember, " Seaver" will become " SEaver" and " Neverland" will become " NEverland" and " Swiss" will become " SWiss". I doubt NW will be an issue.

So use at your own risk, no warranty implied.

[ssanfu]

what would I do to allow only the exceptions of NE, SE, NW, SW, to be read in all uppercase. They always follow the address.

Here is another method:

Private Sub Address_AfterUpdate()
    Dim tmp As String
    Dim tmpAddress As String
    Dim sAddress As String
    Dim dot As String
    Dim Pos As Integer

    Me.Address = StrConv(Me.Address, vbProperCase)
    sAddress = Trim(Me.Address)

    'find last space
    Pos = Len(sAddress) - InStrRev(sAddress, " ")
    'check of there is a period
    dot = Right(sAddress, 1)
    If dot <> "." Then
        dot = "."
    End If

    tmp = Right(sAddress, Pos)
    tmp = Replace(tmp, ".", "")
    If tmp = "nw" Or tmp = "ne" Or tmp = "SW" Or tmp = "SE" Then
        tmp = UCase(tmp)
        tmpAddress = Trim(Left(sAddress, Len(sAddress) - Pos))
        Me.Address = tmpAddress & " " & tmp & dot
    End If

End Sub

[June7]

Was the dot in the address or just period at end of sentence?

If the period is in the address (always):

Address = Replace(Replace(Replace(Replace(StrConv(Address, vbProperCase), " Nw.", " NW."), " Sw.", " SW."), " Ne.", " NE."), " Se.", " SE.")

[ssanfu]

I read it as a dot (period) after the abbreviation, not the end of the sentence.
In the example in post #1, "1234 ohio st. ne." should be converted to "1234 Ohio St. NE."

Now you have me wondering....

[orange]

How about checking space before and/or after in the Replace


...." se " " se. " etc that would avoid SEaver etc.

[June7]

Even if a space is typed at the end, it shouldn't be there in the data. In my experience, Access trims by default.

If there is no dot, the only solution I see that has chance of being accurate is orange's VBA function, maybe without the dot code.