one zip code question and one report with lots of fields question.

[Gilbertjoe]

First question. I have a zip code database with 23000 entries of city name and zip code. Is there a formula I can use to calculate the distances (radius in miles) between any two zip codes?
Second Question. In a Damage Tracking database I have three tables (damage location/damage type/packaging used) that have 20 to 30 yes/no checkbox fields each. I need to run a report based on a query I created from the tables but I keep getting an error stating too many fields. Is there any way to get access to run a report with so many fields involved or do I need to break the report up?

 

[ghubbell]

Hello GilbertJoe!
You might try the USPS for the first part although I doubt they would lend it to you if they had it. Living in Canada I can determine provinces then cities based off of the codes here, but the distances are still a matter of actually entering a number that has nothing to do with the code.

20 check boxes shouldn't be a problem assuming 20?40?60? fields? I guess you use the check boxes for criteria so remember their value is True or False, and if triple state, null. If in your query your asking for "something" where "something = true", something has to be a true or false value. If you want you can use the checks to throw a switch in an "immediate if": IIF(Forms![NameofForm]![ChkBox] = True, "Something","Somethingelse&quot...Give it a shot!

Gord
ghubbell@total.net

 

[MichaelRed]

For your distance calc, it depends on what 'distance' you need. The U.S. Geological Survey has the lat/long of most every thing in the world (literally). This could be used to get a close approximation of the 'straight line' or great circle distances. If you need the 'travel' (or road) distance, it gets to be a lot worse, however it CAN be done. I cannot quite get ny brain to work on it at the moment, buut the Gov. (again!) maintains a map information database which is available for a modest price. It has a lot of information on roadways in the U.S. A few commercial programs are available where you can query the databse with start/destination points And other [Optional] information, and it will return distances (and even routes). The one I have used was "MapInfo", but that was several years ago, and they have changed the name since then.
MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over