MartyConnelly
martyconnelly at shaw.ca
Mon Mar 15 14:01:29 CST 2004
I have code for a soap version but the eraserver.com service has gone
down. Overuse? Probably.
The commercial USPS site uses a proprietary WinInet DLL with a HTTP post
request.
You have to register for the service get license keys and is a bit
awkward to use.
http://www.usps.com/zip4/
http://www.usps.com/webtools/address.htm
Here is code for another commercial site www.cdyne.com cost $0.01 per
inquiry.
There is a test service and I think you are limited to a certain number
of free calls per day
You can use XPath or nodes to parse out the xml returned.
Sub test()
Dim strOut As String
Dim strAddress As String
Dim strCity As String
Dim strState As String
Dim strZip As String
Dim strXMLAll As String
strOut = ""
strXMLAll = ""
strAddress = "3 Quixote Court"
strCity = "Santa Rosab"
strState = "CA"
strZip = "95409"
strOut = AddrCorrect(strAddress, strCity, strState, strZip, strXMLAll)
Debug.Print strOut
Debug.Print "*" & strZip & "*"
End Sub
Public Function AddrCorrect(ByRef address As String, ByRef city As
String, ByRef state As String, ByRef zip As String, ByRef strXMLAll As
String, Optional LicenseKey As String) As String
Dim oXMLHTTP As MSXML2.XMLHTTP
LicenseKey = 0
' Call the web service to get an XML document
Set oXMLHTTP = New MSXML2.XMLHTTP
oXMLHTTP.Open "POST", _
"http://ws.cdyne.com/psaddress/addresslookup.asmx/CheckAddress", False
oXMLHTTP.setRequestHeader "Content-Type", _
"application/x-www-form-urlencoded"
oXMLHTTP.send "AddressLine=" & URLEncode(address) & "&ZipCode=" &
URLEncode(zip) & "&City=" & URLEncode(city) & "&StateAbbrev=" &
URLEncode(state) & "&LicenseKey=" & URLEncode(LicenseKey)
If oXMLHTTP.status <> 200 Then
MsgBox "Service Unavailable. Try again later"
Set oXMLHTTP = Nothing
Exit Function
End If
Dim oDOM As MSXML2.DOMDocument
Set oDOM = oXMLHTTP.responseXML
Debug.Print oXMLHTTP.responseText
strXMLAll = oXMLHTTP.responseText
Dim oNL As MSXML2.IXMLDOMNodeList
Dim oCN As MSXML2.IXMLDOMNode
Dim oCC As MSXML2.IXMLDOMNode
Set oNL = oDOM.getElementsByTagName("Address")
For Each oCN In oNL
For Each oCC In oCN.childNodes
Select Case LCase(oCC.nodeName)
Case "serviceerror"
If CBool(oCC.Text) = True Then
AddrCorrect = "Service Error. Try again Later"
GoTo leaveit
End If
Case "addresserror"
If CBool(oCC.Text) = True Then
AddrCorrect = "Address uncorrectable."
GoTo leaveit
End If
Case "servicecurrentlyunavailable"
If CBool(oCC.Text) = True Then
AddrCorrect = "Service Unavailable. Try again Later"
GoTo leaveit
End If
Case "addressfoundbemorespecific"
If CBool(oCC.Text) = True Then
AddrCorrect = "Address Found. Be more Specific."
GoTo leaveit
End If
Case "deliveryaddress"
address = oCC.Text
Case "city"
city = oCC.Text
Case "stateabbrev"
state = oCC.Text
Case "zipcode"
zip = oCC.Text
End Select
Next
Next
AddrCorrect = "OK" ' Address corrected
leaveit:
Set oCC = Nothing
Set oCN = Nothing
Set oNL = Nothing
Set oDOM = Nothing
Set oXMLHTTP = Nothing
End Function
Public Function URLEncode(inS As String) As String
Dim i As Long
Dim inC, outC As String
For i = 1 To Len(inS)
inC = Mid(inS, i, 1)
Select Case inC
Case " "
outC = "+"
Case "&"
outC = "%38"
Case "!" To "~"
outC = inC
Case Else
outC = "%" + Right("00" + Hex(Asc(inC)), 2)
End Select
URLEncode = URLEncode + outC
Next i
End Function
What it returns.
test
<?xml version="1.0" encoding="utf-8"?>
<Address xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://ws.cdyne.com/">
<ServiceError>false</ServiceError>
<AddressError>false</AddressError>
<Firm />
<DeliveryAddress>3 QUIXOTE CT</DeliveryAddress>
<PrimaryLow>1</PrimaryLow>
<PrimaryHigh>99</PrimaryHigh>
<PriEO>B</PriEO>
<SecEO />
<SecondaryLow />
<SecondaryHigh />
<Secondary />
<Extra />
<City>SANTA ROSA</City>
<StateAbbrev>CA</StateAbbrev>
<ZipCode>95409-4311</ZipCode>
<AddressFoundBeMoreSpecific>false</AddressFoundBeMoreSpecific>
<CarrierRoute>C066</CarrierRoute>
<County>SONOMA</County>
<DeliveryPoint>03</DeliveryPoint>
<CheckDigit>4</CheckDigit>
<BarCode>f9540943114f</BarCode>
<NeededCorrection>true</NeededCorrection>
<CSKey>Z22876</CSKey>
<RecordTypeCode>S</RecordTypeCode>
<CongressDistrictNumber>06</CongressDistrictNumber>
<FIPS>06097</FIPS>
<FinanceNumber>056996</FinanceNumber>
<FromLongitude>-122.644996</FromLongitude>
<FromLatitude>38.467710</FromLatitude>
<ToLongitude>-122.644595</ToLongitude>
<ToLatitude>38.467310</ToLatitude>
<AvgLongitude>-122.6447955</AvgLongitude>
<AvgLatitude>38.467510</AvgLatitude>
<CMSA>7362</CMSA>
<PMSA>7500</PMSA>
<MSA>7500</MSA>
<MA>084</MA>
<TimeZone>PST</TimeZone>
<hasDaylightSavings>true</hasDaylightSavings>
<AreaCode>707</AreaCode>
<LLCertainty>90</LLCertainty>
<CountyNum>97</CountyNum>
<PreferredCityName>SANTA ROSA</PreferredCityName>
<CensusBlockNum>4013</CensusBlockNum>
<CensusTractNum>1526.00</CensusTractNum>
<Primary>3</Primary>
<PrefixDirection> </PrefixDirection>
<StreetName>QUIXOTE</StreetName>
<Suffix>CT </Suffix>
<PostDirection> </PostDirection>
</Address>
OK
*95409-4311*
John W. Colby wrote:
>uhhh... do you have code to do this? I am fresh out of soap, my 3 year old
>son was playing with it and... well.... let's just say he's one clean little
>boy! ;-)
>
>John W. Colby
>www.ColbyConsulting.com
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of MartyConnelly
>Sent: Thursday, March 11, 2004 9:30 PM
>To: Access Developers discussion and problem solving
>Subject: Re: [AccessD] question on normalization
>
>
>There are three or four good web services like USPS that provide this
>info, live via a soap call, of course you need an internet connection.
>You can interrogate these services from Access with just XML and a SOAP
>envelope over HTTP. You dont need all the soap toolkit jazz unless you
>want to write the proxy classes.
>They will also verify that the address is valid too. Such as street name
>spelling and number range. You can also buy the info via quarterly CD's.
>
>
>John W. Colby wrote:
>
>
>
>>David,
>>
>>Did you fill in this table with which Zips were in which town etc.? Or is
>>there a standard db out there somewhere with this stuff?
>>
>>John W. Colby
>>www.ColbyConsulting.com
>>
>>-----Original Message-----
>>From: accessd-bounces at databaseadvisors.com
>>[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of David McAfee
>>Sent: Thursday, March 11, 2004 3:49 PM
>>To: 'Access Developers discussion and problem solving'
>>Subject: RE: [AccessD] question on normalization
>>
>>
>>I our last system, we had a Zipcode table with 5 fields (and 45K+ records):
>>ZipID (PK Autonumber)
>>ZipCode (text,5)
>>City (text, 30)
>>State (text, 2)
>>DefaultCity (yes/no)
>>
>>I would look up a city based on zipcode entry. One of three results could
>>happen:
>>
>>0 records returned: Prompt to check if zipcode entry (later evolved into
>>adding new city)
>>1 record returned: populate city and state onto form
>>
>>
>>
>>
>>>1 record returned: pop up a mini screen with a list box showing all of the
>>>
>>>
>>>
>>>
>>cities using that zipcode. The default city is selected with the OK button
>>having focus.
>>
>>I would store every part of the address together for a given address.
>>
>>In our new system, we have the similar set up as above, but we only keep
>>
>>
>the
>
>
>>ZipID (ID, for the city, state and Zip) with the address.
>>The extra joins needed now are a bit of a pita, so I could imagine how much
>>more it would be to use a junction table containing an CSZid, CityID, Stid
>>
>>
>&
>
>
>>ZipID.
>>
>>I guess it all boils down to what level of normalization do you want to go?
>>3rd, 4th?
>>
>>Oh, btw, are you only dealing with US addresses or INTL addresses too?
>>Because that will add some twists too ;)
>>David
>>
>>
>>
>>-----Original Message-----
>>From: accessd-bounces at databaseadvisors.com
>>[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of John Clark
>>Sent: Thursday, March 11, 2004 12:02 PM
>>To: accessd at databaseadvisors.com
>>Subject: [AccessD] question on normalization
>>
>>
>>quick question:
>>
>>I have a program that will be populated with a 'regional' group of
>>people--this meaning that everyone's address will be within this county.
>>So I am thinking that I can speed up entry, by using a drop-down for zip
>>codes and filling the town name from that. My question is, would it be
>>proper to have a separate table for town and another for zip code, even
>>though zip code would be a single field table? The town table would have
>>three tables, with one being a link to the Zip Code table, and the Zip
>>Code table would have a single field--"Zip".
>>
>>Thanks
>>
>>John W Clark
>>
>>
>>--
>>_______________________________________________
>>AccessD mailing list
>>AccessD at databaseadvisors.com
>>http://databaseadvisors.com/mailman/listinfo/accessd
>>Website: http://www.databaseadvisors.com
>>--
>>_______________________________________________
>>AccessD mailing list
>>AccessD at databaseadvisors.com
>>http://databaseadvisors.com/mailman/listinfo/accessd
>>Website: http://www.databaseadvisors.com
>>
>>
>>
>>
>>
>>
>>
>
>--
>Marty Connelly
>Victoria, B.C.
>Canada
>
>
>
>--
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>
>
>
>
>
--
Marty Connelly
Victoria, B.C.
Canada