Jumat, 21 Agustus 2020

Re: [MSAccessProfessionals] Stripping out a zipcode

maybe add a function
Public Function getZipCode(ByVal strparam As Variant) As String
    Dim strValue As String, i As Integer
    strparam = strparam & vbNullString
    If Len(strparam) > 0 Then
        With CreateObject("VBScript.RegExp")
            .Global = True
            .IgnoreCase = True
            .Pattern = "[a-zA-Z\ ]*"
            strValue = Trim(.Replace(strparam, ""))
            i = InStr(1, strValue, "-")
            If i > 0 Then
                strValue = Left(strValue, i - 1)
            End If
            .Pattern = "\D*"
            strValue = Trim(.Replace(strValue, ""))
            
        End With
        getZipCode = strValue
    End If
End Function

''''
on your worksheet

=getZipCode(C3)

On Sat, Aug 22, 2020 at 12:31 AM jfakes.rm via groups.io <jfakes=rocketmail.com@groups.io> wrote:
My office uses a commercial software package that is the worst designed SQL server in the world.

I'm making a report to group by several fields, right now the zipcode.  I need to extract the zipcode out of the field [JobAddressLine3].  The problem is, the company threw in the city, state, and zip into one field for example:  AURORA CO                80013
There isn't a set number of spaces between the state and the zipcode.  Now I can use a simple code like:  ZipCode: Right([JobAddressLine3],5).  This works fine, except, when the address contains a zip +6 (80111-540577) then my code returns 40577 instead of the 80111.  There are a ton of other variables that I can't control such as punctuation (Denver, CO vs Denver CO for example).

Any ideas on how to strip the zip out taking into account the zip + 6 issue?

I've attached a spreadsheet with a few examples, I've changed the addresses to 5555 for privacy issues.



--
Arnelito G. Puzon


_._,_._,_

Groups.io Links:

You receive all messages sent to this group.

View/Reply Online (#115748) | Reply To Group | Reply To Sender | Mute This Topic | New Topic

Your Subscription | Contact Group Owner | Unsubscribe [sugeng.panjalu.access@blogger.com]

_._,_._,_

Tidak ada komentar:

Posting Komentar