Zip Code Dreams

[Jessica]

As mentioned before, I am working on a data base for a car donation charity.
The non-technical people think it would be really neat if when you typed in
the Zip code in a form, it caused the form to enter the city and state.

Now I have a table of zip, city, state from the 1990 census. I know that if
this is truly implemented I will need a better source of Zip codes. Any
suggestions?

Next is the how to actually make a text field that when you enter a zip code
it looks through a table and pulls the city and state info.

 

[Jeff Boyce]

Jessica

You might want to do a bit more research on Zip Codes. In my area, we have
the same zip code used in two adjacent "cities".

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Ron2006]

Per the Postal service link, they specifically allow for multiple
cities per zip code.

http://zip4.usps.com/zip4/citytown_zip.jsp

I would be affraid to use a 15 year old zip code file - waaaaaay out of
date.

If you had a zip code file then on the dropdown for that zip you would
also want city
or
if unique zips only are shown then check for multiple and if so don't
load city but have it as a combo box itself.

There are commercial sources for the zip code table and some are set up
to work specifically with Access.

google this and you get a big list: zip code lookup access

Updates only cost $$$.
None that I know of are free for updates

 

[UpRider]

Jessica, download file ziplookup.zip from
http://www.dbtc.org/ftp/ziplookup.zip.
It is an example database that has a zipcode table (mostly Colorado, around
Denver) that you can expand. The real meat is the form that allows you to
type in a zipcode which will populate the city and state fields. If the
zipcode matches more than one city, a popup will display the choices and ask
you to select one. You can freely incorporate the code into your
application.

HTH, UpRider

 

[Jessica]

I knew about the zip code data base costing $$$. I warned them about it and
they still want me to try and do this. Both me and my supervisor think that
this is nuts, but I am doing my duty and researching how hard it would be to
implement you enter a zip code in a field and it pull the city and state. I
also just was wondering if any one had used and of the data bases of zip
codes that are offered and if any were more compatible with access than
others.

 

[Ron2006]

From what I saw, most of the packages were rather straight forward.
Some of them implied a free first use download.

And UpRider's example seems to cover the situations you need to cover
along with maintaining the file (adding new records). If you identified
the manually added ones I guess you could periodically check new adds
out for validity - depending on how much you trust your user's input
accuracy. I have the feeling "Spelling" will be the alligator that will
bite you.

I guess part of it is what is the expected life cycle for the
application. Of course there is always the "let's try a different
vendor's free download every year or so." (But make a backup first.)

Good luck.

ron

 

[jahoobob via AccessMonster.com]

You can find a free .csv of zip codes with longitude and latitude here:

http://sourceforge.net/project/showfiles.php?group_id=111073

You can import it to Access and change a few data types to suit.

Here is the great circle equation to calculate distance between two
coordinates that I got from the net:

Distance = 3963.0 * arctan(sqrt(1-x^2))/(sin(lat1/57.2958) * sin(lat2/57.2958)
) + (cos(lat1/57.2958) * cos(lat2/57.2958) * cos(lon2/57.2958 - lon1/57.2958))
)

You can create a form that has two combo boxes for selecting the two zips and
then get the long, and lat to plug into the equation.

 

[jahoobob via AccessMonster.com]

Hold off onthe equation! Three problems:
1)arctan is atn in VBA
2)sqrt is sqr in VBA
3)It doesn't give the correct distance

I'm searching for the problem.

You can find a free .csv of zip codes with longitude and latitude here:

http://sourceforge.net/project/showfiles.php?group_id=111073

You can import it to Access and change a few data types to suit.

Here is the great circle equation to calculate distance between two
coordinates that I got from the net:

Distance = 3963.0 * arctan(sqrt(1-x^2))/(sin(lat1/57.2958) * sin(lat2/57.2958)
) + (cos(lat1/57.2958) * cos(lat2/57.2958) * cos(lon2/57.2958 - lon1/57.2958))
)

You can create a form that has two combo boxes for selecting the two zips and
then get the long, and lat to plug into the equation.

As mentioned before, I am working on a data base for a car donation charity.
The non-technical people think it would be really neat if when you typed in

[quoted text clipped - 6 lines]

Next is the how to actually make a text field that when you enter a zip code
it looks through a table and pulls the city and state info.

 

[jahoobob via AccessMonster.com]

Create a query from your Zips table that has Zip, City, State, longitude,
latitude in that order.
Create and unbound form and place two comboboxes (Combo1 and Combo2) with the
aformentioned query as the source, sorted the way you want. Place the
following code behind the AfterUpdate of the Combo2:

Private Sub Combo2_AfterUpdate()
Dim lon1, lon2, lat1, lat2, radi, GetDistance, x As Double
Dim FrmCity, ToCity, FrmState, ToState As String

radi = 3956 'set the earth's radius
lon1 = Left(Me.Combo1, InStr(Me.Combo1, "/") - 1)
lat1 = Right(Me.Combo1, Len(Me.Combo1) - InStr(Me.Combo1, "/"))
lon2 = Left(Me.Combo2, InStr(Me.Combo2, "/") - 1)
lat2 = Right(Me.Combo2, Len(Me.Combo2) - InStr(Me.Combo2, "/"))
x = (Sin(DegToRads(lat1)) * Sin(DegToRads(lat2)) + Cos(DegToRads(lat1))
* Cos(DegToRads(lat2)) * Cos(Abs((DegToRads(lon2)) - (DegToRads(lon1)))))
' Get Acos(x)
x = Atn((Sqr(1 - x ^ 2)) / x)

FrmCity = Me.Combo1.Column(1)
FrmState = Me.Combo1.Column(2)
ToCity = Me.Combo2.Column(1)
ToState = Me.Combo2.Column(2)
Me.Distance = "From " & FrmCity & ", " & FrmState & " to " & ToCity & ",
" & ToState & " is " & Round(radi * x, 2) & " miles"

End Sub

You will also need two functions:

Function DegToRads(Deg)
DegToRads = CDbl(Deg * pi / 180)
End Function


Function acos(rad)
If Abs(rad) <> 1 Then
acos = pi / 2 - Atn(rad / Sqr(1 - rad * rad))
ElseIf rad = -1 Then
acos = pi
End If
End Function

Zipz.zip at this location:

http://www.census.gov/geo/www/gazetteer/gazette.html

seems to be a pretty comprehensive zip code file. You'l have to change all
the longitudes to negative numbers.

There is a file at that same URL that show the layout of the file.

The mileages I get seem pretty close for zips I entered except for some
places that are close so there is no guarantee on accuracy.
sorry for the previous bad info. Hope this helps.

Hold off onthe equation! Three problems:
1)arctan is atn in VBA
2)sqrt is sqr in VBA
3)It doesn't give the correct distance

I'm searching for the problem.

You can find a free .csv of zip codes with longitude and latitude here:

[quoted text clipped - 17 lines]