X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Access Vba To Lookup + 4 Zip Code, Any Version    
post Mar 13 2020, 11:59 AM

Posts: 88
Joined: 12-April 17

Does anyone have a VBA function that will look-up the plus-4 zip code if we pass in the address, city, state, and zip?
Go to the top of the page
post Mar 13 2020, 12:26 PM

UtterAccess VIP
Posts: 7,386
Joined: 30-June 11

Have you Googled, there are many threads about this. For instance the following looks promising, see: https://answers.microsoft.com/en-us/msoffic...c7-0165869a1260

Daniel Pineault (2010-2020 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc

All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
post Mar 13 2020, 02:02 PM

Posts: 88
Joined: 12-April 17

I've seen several threads, but wasn't sure if any of them were any good. I always like to ask if someone has used it in say the past 12 months to know if it still works smile.gif
Go to the top of the page
post Mar 13 2020, 04:42 PM

Posts: 88
Joined: 12-April 17

@DanielPineault - this isn't working for me...

This is what I have

Function Test()
    ZipCode "13675 Coursey Blvd Apt 1534, Baton Rouge, LA"
End Function
Function ZipCode(Addr1 As String) As String
' Uses USPS website to retrieve 9-digit zipcode
' Takes much longer than Google, but it returns 9 digit zipcode instead of the 5 digit zipcode
    Dim URL As String
    Dim AD As String
    Dim Ct As String
    Dim St As String
    Dim Data As String
    Dim Addr As String
    Dim Zip As String
    Dim I As Integer
    Dim ie As Object
    Dim ieDoc As Object
    Addr = Trim(Addr1)
    I = InStr(1, Addr, ", ")
    If I > 0 Then Addr = Right(Addr, Len(Addr) - I - 1)
    Addr = Addr & ", CA"
    I = InStr(1, Addr, ";")
    AD = Replace(Trim(Left(Addr, I - 1)), " ", "+")
    Ct = Replace(Trim(Right(Addr, Len(Addr) - I - 1)), " ", "+")
    URL = "https://tools.usps.com/go/ZipLookupResultsAction!input.action?resultMode=0&companyName=&address1="
    URL = URL & AD & "&address2=&city=" & Ct & "&state=" & St & "&urbanCode=&postalCode=&zip="
    Set ie = CreateObject("InternetExplorer.Application")
    ie.navigate URL
    Do Until (ie.ReadyState = 4 And Not ie.Busy)
    Set ieDoc = ie.Document
    Data = ieDoc.body.innerText
    Data = Right(Data, Len(Data) - 2400)
    If InStr(1, Data, "Unfortunately, this address wasn't found") > 0 Then
        ZipCode = "Zipcode Error"
        Data = Mid(Data, InStr(1, Data, "Here's the full address") + 94, 100)
        ZipCode = Mid(Data, InStr(1, Data, "-") - 5, 10)
    End If
    Set ie = Nothing
    Set ieDoc = Nothing
End Function

But when the code hits this line
AD = Replace(Trim(Left(Addr, I - 1)), " ", "+")

I get this error ->
Invalid procedure call or argument.
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    11th July 2020 - 12:45 PM