 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / October 2007find all zip codes in a specified radius in an Access database
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. 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? > 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. > 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 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! > > This is a very complex operation and probably not acheivable with a > > Macro. [quoted text clipped - 17 lines] > > Tim F =?Utf-8?B?UGF0IEou?= <PatJ@discussions.microsoft.com> wrote in news:77C56D33-6895-4880-9043-F15C02B67E39@microsoft.com: > 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 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. > > This is a very complex operation and probably not acheivable with a > > Macro. [quoted text clipped - 17 lines] > > Tim F 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? > 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 [quoted text clipped - 6 lines] > > 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. No, unfortunately, I don't. See Tim Ferguson's response, I think he has a better handle on this. > 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 [quoted text clipped - 11 lines] > > > 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. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.