[AccessD] Zipcodes within a radius

John,
Alternatively you can use the following function in your 
query:

Public Function PosdistKM(Lat1, Lon1, Lat2, Lon2)
' Returns distance between 2 positions in Kilometres 
following a great circle route
' The Unit of measure is Nautical Miles if not using a 
conversion constant
' 1 Nautical Miles = 1.852 Kilometres   (NM2KM)
' To express distance in Miles use appropriate constant to 
convert from Nautical Miles
' Lat1, Lon1 - lat and lon for position 1
' Lat2, Lon2 - lat and lon for position 2

    If (Lat1 = Lat2 And Lon1 = Lon2) Then
        PosdistKM = 0
    Else
        Rlat1 = Radians(Lat1)
        Rlat2 = Radians(Lat2)
        Rlon = Radians(Lon2 - Lon1)

        PosdistKM = (60 * (180 / Pi) * arccos(sIn(Rlat1) * 
sIn(Rlat2) + Cos(Rlat1) * Cos(Rlat2) * Cos(Rlon))) * NM2KM
    End If

End Function


Depends on:

Option Compare Database
'Option Explicit

Public Const Pi = 3.14159265358979
' 20070316 bsh Conversion Constant between Nautical Miles 
(NM) and Kilometres (KM)
Public Const NM2KM = 1.852

Public Function arccos(x)
' Computes the arc cosine function
    arccos = Atn(-x / Sqr(-x * x + 1)) + Pi / 2
End Function


Public Function Radians(x)
' Converts from degrees to radians
    Radians = Pi * x / 180#
End Function


Regards
borge

---- Original message ----
>Date: Thu, 22 Mar 2007 18:52:25 -0400
>From: "William Hindman" <wdhindman at dejpolsystems.com>  
>Subject: Re: [AccessD] Zipcodes within a radius  
>To: "Access Developers discussion and problem solving" 
<accessd at databaseadvisors.com>
>
>JC
>
>...first you have to have a zipcode table that includes the 
lat long of each 
>zip ...I have one but its a year old and needs to be 
updated ...on my todo 
>list ...if you need it, I can e it.
>
>...I have this code from an experts exchange thread in my 
code collection 
>but have not gotten around to actually implementing it so 
consider it an 
>aid, not a final solution ...if you do get it working I'd 
appreciate 
>feedback.
>
>...the following gives you a query where you must enter 
your current 
>location (in QueryLatitude and QueryLongitude). It will 
show you all codes 
>sorted by distance with additional fields that tell you 
whether the distance 
>is less than 5, 10, 20 or 50 miles.
>
>It's a series of 4 queries.
>
>Query qDistance1: Converts all distances from degrees to 
radians. Input is 
>table tPostcode.
>
>SELECT tPostcodes.Areacode, [Latitude]*3.1415926/180 AS 
LatRad, 
>[Longitude]*3.1415926/180 AS LonRad, [QueryLatitude]
*3.1415926/180 AS 
>QLatRad, [QueryLongitude]*3.1415926/180 AS QLonRad, 
tPostcodes.Latitude, 
>tPostcodes.Longitude
>FROM tPostcodes;
>
>Query qDistance2: Calculates the ArcCos of the distance 
between each 
>postcode office and the coordinates (QueryLatitude, 
QueryLongitude) 
>according to the formulae you were given.
>
>SELECT qDistance1.Areacode, 
>Sin([LatRad])*Sin([QLatRad])+Cos([LatRad])*Cos([QLatRad])
*Cos([LonRad]-[QLonRad]) 
>AS cosd
>FROM qDistance1;
>
>Query qDistance3: Calculates the distance in miles. This 
extra step is 
>required because Access doesn't support the ACos function, 
so we have to 
>calculate ACos through ATan.
>
>SELECT qDistance2.Areacode, 
>Abs(1.852/1.61*60*180/3.141526*2*Atn(Sqr((1-[cosd])/(1+
[cosd])))) AS 
>DistMiles
>FROM qDistance2
>ORDER BY Abs(1.852/1.61*60*180/3.141526*2*Atn(Sqr((1-
[cosd])/(1+[cosd]))));
>
>
>Query qDistance4: Finds out whether a post office is in the 
range of 5, 10, 
>20 or 50 miles within the post office.
>
>SELECT qDistance3.Areacode, qDistance3.DistMiles, 
[DistMiles]<5 AS Dist05, 
>[DistMiles]<10 AS Dist10, [DistMiles]<20 AS Dist20, 
[DistMiles]<50 AS Dist50
>FROM qDistance3;
>
>How to use this:
>Execute qDistance4 by passing QueryLatitude and 
QueryLongitude as 
>parameters. From the result set, choose those results that 
satisfy your 
>criteria, i.e. Dist5, Dist10, Dist20 or Dist50 are set.
>
>...hth
>
>William Hindman
>----- Original Message ----- 
>From: "JWColby" <jwcolby at colbyconsulting.com>
>To: "'Access Developers discussion and problem solving'" 
><accessd at databaseadvisors.com>
>Sent: Thursday, March 22, 2007 3:42 PM
>Subject: [AccessD] Zipcodes within a radius
>
>
>> Does anyone know how to get the zip codes with a radius 
of an address?
>>
>> John W. Colby
>> Colby Consulting
>> www.ColbyConsulting.com
>>
>> -- 
>> 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