Re: [MSAccessProfessionals] Stripping out a zipcode

This is what I ended up with and it works:

Left(Mid(Nz([JobAddressLine3],""),InstrRev(Nz([JobAddressLine3],"")," ")+1),5)

 

This assumes that everything after the last space in [JobAddressLine3] is the zip code. If JobAddressLine3 is null, this expression will return an empty string.

Thanks.

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]

_._,_._,_

Re: [MSAccessProfessionals] Stripping out a zipcode

Sorry I thought the back end is SQL server. It's not in Access.

On Fri, Aug 21, 2020 at 02:10 PM, Duane Hookom wrote:

I don’t believe PatIndex() is available in MS Access like it is in SQL server. 

 

Duane

Sent from my mobile

On Aug 21, 2020, at 12:46 PM, Wei Qian via groups.io <wei_qian2001=yahoo.com@groups.io> wrote:

You can use PatIndex() function to find the position of "-", then use left(), right(), or substring() to strip out zip code.  ~ Wei

_._,_._,_

---

Groups.io Links:

You receive all messages sent to this group.

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

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

_._,_._,_

Re: [MSAccessProfessionals] Stripping out a zipcode

I don't believe PatIndex() is available in MS Access like it is in SQL server. 

Duane

Sent from my mobile

On Aug 21, 2020, at 12:46 PM, Wei Qian via groups.io <wei_qian2001=yahoo.com@groups.io> wrote:

You can use PatIndex() function to find the position of "-", then use left(), right(), or substring() to strip out zip code.  ~ Wei

Re: [MSAccessProfessionals] Stripping out a zipcode

You can use PatIndex() function to find the position of "-", then use left(), right(), or substring() to strip out zip code.  ~ Wei

_._,_._,_

---

Groups.io Links:

You receive all messages sent to this group.

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

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

_._,_._,_

Re: [MSAccessProfessionals] Stripping out a zipcode

Hi jfakes,

You can try use a combination of Right(), InstrRev(), and Len().

RIght(JobAddressLine3, Len(JobAddressLine3)-InstrRev(JobAddressLine3," "))

Regards,

Duane

---

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of jfakes.rm via groups.io <jfakes=rocketmail.com@groups.io>
Sent: Friday, August 21, 2020 11:30 AM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: [MSAccessProfessionals] Stripping out a zipcode

 

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.

[MSAccessProfessionals] Stripping out a zipcode

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.

_._,_._,_

---

Groups.io Links:

You receive all messages sent to this group.

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

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

_._,_._,_

[belajar-access] File - Tata Tertib dan Aturan.txt

 

Tata tertib dan peraturan
Mailing List: belajar-access@yahoogroups.com

1. Mailing list ini membahas mengenai pemrograman Microsoft Access.
2. Tidak diperkenankan mem-posting topik yang tidak ada kaitannya sama sekali dengan pemrograman MS Access, peluang kerja atau tawaran kerja sama dengan keahlian di bidang MS Access, atau pengajaran/kursus MS Acces. Pelanggaran terhadap aturan ini akan di-ban dari keanggotaan milis ini.
3. Mohon berdiskusi dengan baik, dengan semangat membangun, demi kemajuan kita bersama. Hindarilah perbantahan (flame) yang bisa menjadi pertengkaran yang tidak perlu.
4 Hindari reply permintaan one-liner seperti 'saya minta juga dong', 'saya setuju', dan lain-lain yang tidak perlu.
5. Sedapat mungkin memberikan data-data yang lengkap dalam mengajukan suatu masalah untuk memudahkan rekan-rekan sesama member mengidentifikasi dan mencarikan solusi, termasuk memberikan subject yang sesuai dengan isi email, tidak dengan kata-kata seperti "tologing dong", "pusing...", "ada yang bisa bantu..", dll.

Moderator

__._,_.___

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (161)

SPAM IS PROHIBITED

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___