find all zip codes in a specified radius in an Access database

[Pat J.]

How do I create a macro that lets me key in a zip code and a specified radius
in order to see a subset of all records in my Access database that fall
within that radius? I am not an experienced programmer. Thank you.

 

[Klatuu]

This is a very complex operation and probably not acheivable with a Macro.
When you say radius, I am understanding you to say, for example, I want zip
code 12345 and all zip codes withing 25 miles. So right away, you can see
the problem. How do I know the distance of any zip code from any other zip
code.
Do you have a table designed that will give you that information?

 

[Tim Ferguson]

This is a very complex operation and probably not acheivable with a
Macro.

If there is a list of x- and y- coordinates for the (centre of) the
zipcodes, then it's elementary arithmetic. For distances of less than a
hundred miles, I guess you could leave out all the complex spherical
geometry and just go with pythagoras.

PARAMETERS TargetEasting NUMERIC, TargetNorthing NUMERIC;
SELECT ALL AreaName, NumericCode
FROM ZipCodeAreas
WHERE 625 > (
(Easting - TargetEasting)^2 + (Northing - TargetNorthing)^2
)
ORDER BY NumericCode;


.... or something along those lines.

B Wishes


Tim F

 

[Pat J.]

I do see the problem with needing to have the zip radius data in a field.
Right now I use Zip Distance Wizard to find this radius info and then check
records in my database against the resulting list of cities. Do you know of
any software I could use with Access itself?

 

[Pat J.]

Tim,

Thank you for your response. Right now I use Zip Distance Wizard or Zipoid
and then check my database records against the resulting list. As programming
is all Greek to me, I think I'll just keep on doing this. ZDW and Zipoid do a
great job of reporting the cities I'm looking for. I had hoped to eliminate
the 2nd step of checking a list against my database. Thanks, anyway!

 

[Klatuu]

No, unfortunately, I don't. See Tim Ferguson's response, I think he has a
better handle on this.

 

[Tim Ferguson]

Right now I use Zip Distance Wizard or Zipoid
and then check my database records against the resulting list.

I cannot find any reference to a table of zip codes and their geographic
locations; but then again I don't interact with US addressses very often!
It would seem to me likely that someone somewhere has taken the time to
type in a list of grid co-ordinates for each code. If not, then there has
to be a huge market for such a list.

As indicated above, once you have such a list, the rest is elementary
arithmetic. Without it, I have no idea how it could be done.

Best of luck


Tim F

 

[efandango]

Tim,

when you say "there is a list of x- and y- coordinates for the (centre of)
the

zipcodes"; do you mean Lat & Lon gecodes?; if so, I have a list of Lat/Lon codes for my postcode table (UK). Will your formula work on these codes?. Ideally I want to run the formula in a query, where I want to say to the query, find all addresses (geocode) within a 2 mile radius of a given geocode (which is on the parent form).

can you help with this. I can do simple VBA, but not great with maths.