calculate the driving distance between 2 zip codes (1 Viewer)

[LucyK]

Thank you Jack!
Yes, we will probably be doing it on a regular basis. Depending on that we will probably sign up for an account.

Thank you so much for the info

Lucy

 

[jdraw]

You might try talking with Google and explain your situation specifically. There may be some options that can accommodate your needs.
Good luck.

Let us know what you finally decide.

 

[LucyK]

Hi Jack,

I had about 5,000 more to do, so I did it in 3 days. That worked for now and I don't think I will have more to do in a while.

Thanks again for your help and the file. It really helped me a lot.

 

[jdraw]

Glad you have it resolved. Good luck with your projects.

 

[jdraw]

I have modified the database included in the zip file attached. I noticed the attempt to get the distance between a LatLon and a Zip was no longer working. I think Google has changed the parameter string format. Anyway I have added a new form to test combinations of zips, addresses and LatLon and produce a map. This new form includes an option to select miles/kilometers. Only the code to produce a Google map of the route between 2 entities on the new form has changed.

Note: There is no change to the GetDistanceBetweenTwoZips.

 

[gemma-the-husky]

lucy. you could consider buying MS mappoint, as an alternative.

very flexible application, you can use the mappoint library as you use excel say.

inexpensive, and a good alternative for a business.

 

[Duckman]

Could one just buy a dataset with all pairs of zip codes and the driving times between them?

 

[jdraw]

Duckman,
I'm sure one could buy such a dataset, if one could find a source. You might try to create one, but you might want to review this first.

 

[Duckman]

Jdraw, it occurs to me that drive times depend on time of day, because of changing traffic conditions. Does your Access sheet return some sort of average drive time?

 

[jdraw]

The database that I provided calls Google Maps to get driving distances and time. Google Maps internals may deal with traffic conditions, but I have not investigated that.

 

[JC_OP]

Do you have a version that process from two tables? example TABLE_1 contains Agent, AccountId, Zip. TABLE_2 contains AcountId, CustomerName, Zip. Match on AccountId to leverage your google map routine.

 

[jdraw]

No I do not have 2 tables as such. Take a look at the database in post#5. I think you will be able to see how the zip codes work. Download and review the databases attached to posts in this thread. Or start a new thread and in your post describe to readers what you are trying to do (in plain English). Then show readers your work so far; and ask specific questions based on your issue/opportunity and your current development.

 

[viddi1232]

hi everyone i have stumbled across this thrad and find it great my only question is can you get the results with out the mi at the end as want to enter this into a number field to later calculate on my form. so for example result says 15 mi and driving time 12 mins i would like to feed these into a field set as number so then can do a calculation on total

 

[jdraw]

Did you try? What result?

 

[viddi1232]

hi thanks for the reply

i have resolved the issue as was making things more awkerd for myself i changed

sDistance = Mid(sDistance, lTopicstart + 6, lTopicend - lTopicstart - 6)

to

sDistance = Mid(sDistance, lTopicstart + 6, lTopicend - lTopicstart - 8)

this removes the mi at the end of the milage/distance result and then allows me to put it into numberd field that will rounfd it up if returns a a result such as 5.5 to 6

all i needed was a fresh cup of coffee

and have to say agin love this code as has multied of uses for me

 

[jdraw]

Good stuff glad you have it resolved, and that the code was helpful.

 

[klumzie]

Hi Thanks for the code it works wonderfully but i would like to change it so rather than a message box popup i would like it to automatically update fields in a table.

I want to use the code to calculate my driving distance from my house (zipcode1 Fixed) to various different address's (zipcode2 Variable) and then to input the results into a table.

I'm new to VBA coding and not sure how to do this could some please advise me how to do this.


Thanks a lot.

 

[rach_rockstar]

I am attaching a zip file containing an Access mdb database. The database has a table of 27 random US zips and a form -frmZipSample. Using the form you can select 2 zips and at the click of a button get a message showing
a) the distance between the zips, and
b) the estimated driving time.

There is an option to show the route in a separate browser window. I use Firefox 12.

This is for illustration only. Google Maps is the property of Google.

Could this be adapted to use Lat & Lons?
I have the post codes and lats&longs of all the places I need to be able to go between and struggling to find the best way to get the desired output!

 

[jdraw]

rach_rockstar,
The code will do that. Here is a screen shot showing LatLon and Street Address.
I just noticed your post, downloaded the latest database and tested the combination.
It showed the distance(293 km) and time(2 hr and 54 min) and offered me an option to show the route on a google map.

Good luck.

 

[shadow9449]

Does anyone know if this still works? I've been using it since 2015 and recently it's been inconsistent about being able to find addresses and routes.

Thank you