calculate the driving distance between 2 zip codes

[newuser262]

Hi jdraw,

I've looked at the zip code database you provided and had a couple of questions.

1. I've tried to replace the random 27 zip with coordinates and change the associated VBA code. Can't seem to get it to work. I know the "from" and "to" coordinates work with google maps so it should be just as straightforward.

Here are a two of the coordinates:
28.8947545 -99.1212434
32.0358002 -84.7718622

2. You mention bypassing manual intervention and running a loop. Can't seem to get that to work either. What I'd want is to click a button and have the driving distances/times populate one-by-one for all zip code (or in my case, coordinate) combinations. Possible??

 

[jdraw]

I took your lat and lon values and used a small function Distance to find the distance between these points in miles

Lat1 =28.8947545 
Lon1 =-99.1212434  
Lat2=32.0358002
Lon2 = -84.7718622
?Distance(lat1,lon1,lat2,lon2,"m")
 880.878193054651

Let me know if this is what you're looking for.

Note: These are distances (as the crow flies). These are not driving distances and do not use Google maps.

I'm sure Google maps can get you driving distance and times but I don't have time at the moment to look into this further.

Edit: I just tried entering your lat/lon of points into Google maps.
see attached jpg

 

[newuser262]

Thanks for the quick reply. All I really need is to be able to substitute coordinates for the zip codes in the database you provided in post #5, and bypass the form so that all distances pulled from google maps calculate automatically. Modified the table in the database and tried to modify the code with little success.

 

[jdraw]

I took a quick look at the code tonight.

The code allows 2 zips, or 2 addresses or 2 sets of coordinates.

'NOTE: From Google DistanceMatrix:
' You may pass either an address or a latitude/longitude coordinate with the origins
'and destinations parameters. If you pass an address as a string, the service will geocode
'the string and convert it to a latitude-longitude coordinate to calculate directions.
'If you pass coordinates, ensure that no space exists between the latitude and longitude values.

I used the routine from the database in post #5 to do the following

Sub testLatlon()
Dim myLat1 As String
Dim myLon1 As String

Dim myLat2 As String
Dim myLon2 As String

myLat1 = "28.8947545"
myLon1 = "-99.1212434"
myLat2 = "32.0358002"
myLon2 = "-84.7718622"
Debug.Print GetDistanceBetweenTwoZips(myLat1 & "," & myLon1, myLat2 & "," & myLon2)

End Sub

GetDistanceBetweenTwoZips is the main routine in the database.
Debug.print produced the following

1--------- sDistance 991 mi
2------ sDuration 16 hours 33 mins
991 mi|16 hours 33 mins

991 mi|16 hours 33 mins is the output of the GetDistanceBetweenTwoZips procedure

 

[jdraw]

Thanks but that is Totally No Good for me...

I'm looking for addresses that are very Close to each other,
like 1/20th of a mile...

Victor

I used the following Lat/Lon coords
(1) "41.70900431, -83.585444"
(2) "41.70900431, -83.5854439996183"

and found these points were 1 ft apart.

 

[highandwild]

Hi JDraw

I have a similar project so was very interested in your database.

I downloaded the database and added DT1 and MK41 as UK Postcodes.

The distance returned was fine but the map produced included locations in the USA and the UK. I am not sure why this is and is there any way of only the UK locations?

Thanks.

 

[jdraw]

I just ran your data points into the database and got the output
169 mi 3 hrs and 19 minutes. The map as you said is basically a world map.

The rationale: Google shows you on the map that he/she is confused - do you mean Ft. Lauderdale FL etc.
If you provide more distinct coordinates
Dorchester DT1, UK to Bedford MK41, United Kingdom you get
169 mi 3 hours and 17 minutes with proper map as below.

Google maps is not guessing to what the Postcode refers.

 

[highandwild]

Thanks a lot jdraw.

I will play around later and implement it.

I find this forum so informative and I love reading about the projects that others are involved but it is often so evident that people go through frustrating times to get things right.

HighandWIld
Jurassic Coast
England

 

[jdraw]

FYI,
I have found that the more detail (less ambiguity) in your map coordinates/postcodes, the better the output.

Good luck.

 

[highandwild]

The pleasure comes from sharing on here and developing applications which change peoples lives and release them for more people centered activities.

Client coming round tomorrow and I expect a few smiles.

 

[gemma-the-husky]

FYI,
I have found that the more detail (less ambiguity) in your map coordinates/postcodes, the better the output.

Good luck.

yes - the kml file that google returns includes a "precision" factor indicating the level of accuracy to which it was able to geocode the address/post code

from memory 1 and 2 are pretty accurate, but 3 and above are often not sufficiently accurate.

 

[marvinhagen]

Jdraw,

Thanks so much for this file. It works great. I did have one question....sometimes an error results between two zip codes that do show up in Google when entered manually. For example, when I use 21740 for zip1 and 26847 for zip2. Do you have any ideas as to why this would happen? Thanks again.

 

[jdraw]

Just saw your post when reviewing this older thread for another poster.

Yes, Google maps is confused when you input 21740. If you look at google maps with this value, it shows a list of what it found/will use and asks which do you mean. If you just go merrily along, it will use the first value -- I got Aland Islands.

Similarly with the second zip, it gives a list of what you might mean and asks you to select. If you just type enter it uses the first value it showed you.

You really have to as much info about your zip as possible (ZIP/State), and test your samples. Otherwise you will get Google maps best guess, and that may be way off what you were thinking.

 

[marvinhagen]

JDraw,

I've been using this code a lot for various things so again...thank you. However, because I've been using it so much, I've been running into the daily limits in place by Google of 2,500. I found this quote online regarding the limits "The new usage limits will allow you to generate up to 25,000 map loads per day per API, and up to 2,500 map loads for "styled maps"." Do you or anyone else know how to change the code to not use "styled maps" so that I can get more each day? I'm only using to calculate batch travel duration from two zip codes so the detailed map isn't important. Any help would be greatly appreciated.

Marv

 

[wnicole]

Hi,
I just have one question.
Instead of entering the postal code continually. How do I set zipcode 1 and zipcode 2 based on to fields in a form. I would like to use the current FROM (Default zip based on the current job in form) and TO zipcode [Project Postal Code] that is on the form at that time.

Currently you have
zip1 = Me.Combo2.Value
zip2 = Me.Combo4.Value

I want Zip 1 to be Default zip (however I can still change it if needed)
ZipCode field is [Project Postal Code]

Can you help me? Not that savvy with replacing. code.
Thanks

 

[jdraw]

It would be easier if you posted some code.

Note: The sample form I provided was just a sample. The intent was to show the use of Google Maps from Access/vba. You can have your own form and use the GetDistanceBetweenTwoZips function.

In general you could use an inputbox or msgbox and ask the user what the default zip should be then validate it and place it in a variable of your choice. Use the variable in place of zip1 in your logic.

see http://office.microsoft.com/en-ca/access-help/inputbox-function-HA001228856.aspx

Good luck.

 

[SP2ewe]

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.

Hi jdraw, I was hoping you may be able to help me? I downloaded your
DistanceBetweenZips_0.zip and wish to use it in my data base, it would greatly help me with my clients, I wish to use full address so I can quote the correct amount I need to charge for traveling, is there a way I can change Ml to KM and also have Messagebox information but this information into my clients table?
I look forward in hearing from you and thank you ever much your work is best
Cheers Steve The Shearer

 

[jdraw]

sp2ewe,

I have revised the database based on your needs.
There is a sample form now that includes 2 addresses.
If you look at the function in the module you'll see that I have added an optional parameter sUnits If you call the function with your addresses
eg (addr1, addr2) you can add the optional parameter to get results in kilometres
eg (addr1, addr2,"K")

The default distance is Miles.

There are test routines within the module.

NOTE:

This zip and database have the same names as before, but the database has been updated @13Mar2014.

GoogleMaps is the property of Google.

 

[SP2ewe]

Hi jdraw I have love the revised database I just changed the tbl name and field to suit my Customer Table & fields in my database and it works well also very helpful in just being able to see the “KM” and “Time” information for address to a client. But I is there a way that I can get the popup Messagebox information for the “KM” and “Time to Auto submitted or a command button that allow me to submitted the “KM” and “Time” information to my Customer table? I have two Fields in my Customer table Called; KmTo & EtaTo
Cheers Steve The Shearer

sp2ewe,

I have revised the database based on your needs.
There is a sample form now that includes 2 addresses.
If you look at the function in the module you'll see that I have added an optional parameter sUnits If you call the function with your addresses
eg (addr1, addr2) you can add the optional parameter to get results in kilometres
eg (addr1, addr2,"K")

The default distance is Miles.

There are test routines within the module.

NOTE:

This zip and database have the same names as before, but the database has been updated @13Mar2014.

GoogleMaps is the property of Google.

 

[jdraw]

SP2ewe,

But I is there a way that I can get the popup Messagebox information for the “KM” and “Time to Auto submitted or a command button that allow me to submitted the “KM” and “Time” information to my Customer table? I have two Fields in my Customer table Called; KmTo & EtaTo

I'm sure there is. How comfortable are you with vba?
Can you post your table design?
Did you look at some of the comments within the module?

The output of the function is a string.
Typical output is 991 mi|16 hours 33 mins.

The format of the output is a 2 part string with an embedded "|". To the left is the Distance. To the right is the Duration.

So if you consider 3 string variables within your code.

  Dim strDistance as String
  Dim strDuration as String
  Dim sHold As String
 sHold = GetDistanceBetweenTwoZips(addr1, addr2,"K")
 sDistance = Mid(sHold, 1, InStr(sHold, "|") - 1)
 sDuration = Mid(sHold, InStr(sHold, "|") + 1)

[COLOR="SeaGreen"]''''In your case[/COLOR]  
KmTo = sDistance
EtaTo = sDuration

 MsgBox "The distance between " & addr1 & " and " & addr2 & " is " & Mid(sHold, 1, InStr(sHold, "|") - 1)
MsgBox " The estimated time to get there is " & Mid(sHold, InStr(sHold, "|") + 1)

Good luck.