To build a ZIP Code radius search database in Microsoft Access, you must calculate mathematical “straight-line” distances between geographical coordinates. Because Access does not have built-in geospatial mapping engine data types, you must use Visual Basic for Applications (VBA) alongside a comprehensive table of coordinates.
Here is the comprehensive guide to building this system step by step. Step 1: Secure a Coordinates Database
You cannot calculate distances with ZIP codes alone. You need a data source that pairs every target ZIP code with its specific Latitude and Longitude centroids.
Download a comprehensive US ZIP code dataset containing ZipCode, Latitude, and Longitude. Free and paid databases are widely available on community platforms like GitHub or data providers like FMS Inc.. Create a table in your Access database named tbl_ZipCodes.
Configure the fields exactly as follows to prevent dropping leading zeros: ZipCode: Short Text (Primary Key) Latitude: Number (Double) Longitude: Number (Double) Step 2: Implement the Distance Formula (VBA)
To convert coordinates into a mileage radius, you must implement the Haversine formula or Great-Circle distance formula via an Access VBA module.
Press ALT + F11 to open the VBA Editor inside Microsoft Access. Click Insert > Module.
Copy and paste the following mathematical function into the window:
Public Function CalculateDistance(Lat1 As Double, Lon1 As Double, Lat2 As Double, Lon2 As Double) As Double Dim InchesPerRad As Double Dim RadLat1 As Double, RadLon1 As Double Dim RadLat2 As Double, RadLon2 As Double Dim dLon As Double, dLat As Double Dim a As Double, c As Double Dim RadiusOfEarth As Double ‘ Earth’s radius in miles. Change to 6371 for kilometers. RadiusOfEarth = 3958.8 ’ Convert degrees to radians InchesPerRad = 3.14159265358979 / 180 RadLat1 = Lat1InchesPerRad RadLon1 = Lon1 * InchesPerRad RadLat2 = Lat2 * InchesPerRad RadLon2 = Lon2 * InchesPerRad ‘ Haversine calculation dLon = RadLon2 - RadLon1 dLat = RadLat2 - RadLat1 a = (Sin(dLat / 2) ^ 2) + Cos(RadLat1) * Cos(RadLat2) * (Sin(dLon / 2) ^ 2) c = 2 * Atn(Sqr(a) / Sqr(1 - a)) CalculateDistance = RadiusOfEarth * c End Function Use code with caution. Save the module as mod_DistanceCalculator. Step 3: Build the Radius Parameter Query
You will need a query that compares a single targeted starting ZIP code against all other ZIP codes in your master list, calculating the mileage between them on the fly. Go to the Create tab and click Query Design.
Close the table picker without adding a table. Right-click the design grid and switch to SQL View. Paste the following structured query script:
PARAMETERS [Enter Target ZIP] Text, [Enter Max Radius in Miles] Double; SELECT Destination.ZipCode, Destination.Latitude, Destination.Longitude, CalculateDistance(Origin.Latitude, Origin.Longitude, Destination.Latitude, Destination.Longitude) AS DistanceFromTarget FROM tbl_ZipCodes AS Origin, tbl_ZipCodes AS Destination WHERE (((Origin.ZipCode)=[Enter Target ZIP]) AND ((CalculateDistance(Origin.Latitude, Origin.Longitude, Destination.Latitude, Destination.Longitude))<=[Enter Max Radius in Miles])); Use code with caution. Save the query as qry_ZipRadiusSearch.
Performance Note: This technique utilizes a Cross Join (Cartesian Product) to compare the origin record against all destination records. For large databases with over 40,000 national ZIP codes, Access may lag. For faster execution, apply a rough coordinate bounding-box constraint in the WHERE clause prior to processing the VBA function. Step 4: Link Your Customer or Contact Data
To find people or business leads within that radius, you can join this geographic logic straight to your primary operational data. zip code lookup for Web database – Microsoft Q&A
Leave a Reply