calculate the driving distance between 2 zip codes

[SP2ewe]

Hi Jack, thanks for getting back to me so quickly. Truthfully Im not the best at vba Im just a shearer and all self taught in VBA Im also bit unsure how to post my table design here I did try but it would not allow me to upload, it keeps saying

"Your file of 2.46 MB bytes exceeds the forum's limit of 2.00 MB for this filetype. " the file type is Zip Customer.mdb and only has a Customer Table within it and is still to big to send here.

Regarding code you sent me "3 string variables code above, where should I put this Code? within the Form or in the Moldue to make it work Im just a little unsure where to place the code to make it work so it allow me to submitted the “KM” and “Time” information to my Customer table Fields Called; KmTo & EtaTo?
Cheers Steve The Shearer

 

[jdraw]

You should do a Compact and Repair of your database to remove used space. Access will keep deleted records, queries, tables etc until you do a compact and repair to remove them and gain that space back for your use.

Then use the WinZip utility to make as zip (compressed file).
How many addresses do you have that you want to get distance and duration?

 

[SP2ewe]

Thanks Jack what you told me to do worked, here's a copy of Customer Table.

I have over 1000 clients in the database where I have to get the KmTo & EtaTo

Att; Customer.mdb the has the Table to customer, last to Fields are KmTo & EtaTo

Cheers Steve The Shearrer

 

[jdraw]

Steve,

You have modified the form I had in the sample by adding KmTo and ETA. But you have not included the module, so the click of the button just gives an error "Sub or Function not found".

I am going to be very frank and tell you that your data base design is "awful". If you are using this with a business, you will be constantly fighting the database system because of the lack of Normalized tables.

It is critical for a database to support your business efficiently and to be easily maintained that your tables and relationships match you business rules.

A one table database with Customers, vaccinations, callHistory, callHistory1, accountBalance and nextServiceDate is just awaiting disaster.

Tell me about your business. What do you do as a business. If you give me some details, I'll help you with some potential design changes. As I understand it you want the distance from your location/home base to the various customers, is that correct?

I'll recreate the module and get the form working, but your database needs to be redesigned.

jack

 

[jdraw]

Steve,

I recreated the module. It is now customized for you. I modified the Form. Clicking the button now updates the record in the Customer table and shows the values in the text boxes on the form.

I edited your customer record to change Mount Vermon Rd to Mount Vernon Rd.

Good luck.

You really need to redesign that database, It will cause you grief.

NOTE: For anyone following this, the function calculates the distance and duration for driving between 2 zips, 2 addresses, 2 lat/long or any combination.

 

[jdraw]

Steve,

How is it going? Have you looked at the changes?

 

[SP2ewe]

Hi Jack, sorry for taking so long to get back to you, I been away doing some work shearing over the last few days, I have just downloaded your latest DB and it looks good. Ill try and get it into my DB over this week and have a good look at it. I know it will work, but as you state I also know I should have a much better design for my Customer table, I was only all new at this many years ago and started off with a Excell Invoice system that connects to MS Access Database made and download from;

Www office-kit.com/excel_invoice_manager

It is a great bit of software and I have used this excel_inoice_manager for many 5 years and YES have added many other functions to it as I was learning and I must say Im still learning.

I do a lot of work on the road, I have tried to build a full contact management system that allows the excel_inoice to contacts to my Toughbook GPS and get the true Lat/Long to a client and updates all the clients records in about 7 different Tables that are in the DB and 3 other mapping and navigation software, also the DB connects to MS Outlook which sync all current contacts and appointments to all Mobiles Phone’s, MS Outlook & the DB only the most current record is updated in any directions and corrects all information in the DB. I also have keep all Call History/SMS and Voice Recording History form my Nokia Phone, which is Imported into a file on the system and connects to the DB to any history of a new, or past or current clients, it rescans the full call/sms and voice recording to all customers/clients where I can play all call recording and see full details of every incoming and outgoing call/sms to that occurs to that customer. Its dose a lot more and I keep trying to improve it but I feel I may have over developed it.

Mate I wish you where here in Au would love to show you.Look faward to talking to you soon.
Cheers Steve The Shearer.

 

[jdraw]

Steve,
Thanks for getting back. If it applies, the function for the Distance between Zipcodes, will work fine for a Lat/Long and an address; 2 lat/longs etc.

Cheers.

 

[LucyK]

Hello,

I came across this forum and found almost what I needed
I need to calculate distance between 1 zip code (Zip_From) and Multiple To Zip codes. I have an Excel file with the to and from distance and would like to be able to modify the Access application you posted.

But instead of entering the 2 zips manually, I would like the system to look the Zip codes table (attached) and populate the Distance and Estimated time.
Is this possible?

Thank you so much in advance

 

[jdraw]

There is no zip table attached.
You can have the from / to zips in a table and call the function accordingly. You'll need a little vba to pull out the zips and call the function.

Good luck with your project.

 

[LucyK]

Thank you for your prompt reply.
I guess that is were my trouble is...to modify the script.
I would need to make more research on this.

 

[LucyK]

Here is my Excel file

 

[jdraw]

The function requires string variables. So I converted your ZipFrom to string /text. I tried to link your Excel sheet but I cannot update a linked Excel file. So then I imported the revised sheet and named it Sheet1_LucyImported.
I used a small procedure

'---------------------------------------------------------------------------------------
' Procedure : RoutineForLucyK
' Author    : Jack
' Date      : 01/08/2014
' Purpose   : LucyK had request to find distance between columns in excel sheet.
'see http://www.access-programmers.co.uk/forums/showthread.php?t=225339&page=4#49
'
'Had to modify the number field ZipFrom to Text
'Had to import the data into Sheet1_LucyImported
'
'---------------------------------------------------------------------------------------
'
Sub RoutineForLucyK()
          Dim db As DAO.Database
          Dim rs As DAO.Recordset
          Dim MyResult As Variant
         
10        Set db = CurrentDb
20        Set rs = db.OpenRecordset("Sheet1_LucyImported")
30        Do While Not rs.EOF
40            rs.Edit
50            MyResult = GetDistanceBetweenTwoZips(rs!ZipFrom, rs!ZipTo)

'These Debug.Print statements could be removed

60            Debug.Print "Distance  " & Mid(MyResult, 1, InStr(MyResult, "|") - 1)

70            Debug.Print "Time      " & Mid(MyResult, InStr(MyResult, "|") + 1)

              ' Discovered embedded spaces in field names
             'Showing 2 ways to reference field in recordset

80            rs("Distance _Miles") = Mid(MyResult, 1, InStr(MyResult, "|") - 1)
90            rs![Estimated Time] = Mid(MyResult, InStr(MyResult, "|") + 1)
100           rs.Update                  'Update the field in the record
110           rs.MoveNext              'Move to next record in recordset
120       Loop

130       On Error GoTo RoutineForLucyK_Error



140       On Error GoTo 0
150       Exit Sub

RoutineForLucyK_Error:

160       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure RoutineForLucyK of Module GetDistanceStuffForSteve"
End Sub

and got these results

ZipFrom	ZipTo	Distance _Miles	Estimated Time
92614	92814	16.7 mi	23 mins
92614	92815	15.2 mi	20 mins
92614	92816	15.3 mi	19 mins
92614	92817	16.1 mi	19 mins
92614	92825	14.3 mi	18 mins
92614	92831	18.7 mi	23 mins
92614	92832	19.4 mi	26 mins
92614	92834	20.6 mi	25 mins
92614	92836	20.9 mi	24 mins
92614	92837	19.2 mi	24 mins
92614	92838	20.2 mi	28 mins
92614	92840	14.3 mi	21 mins
92614	92841	14.3 mi	21 mins
92614	92842	13.1 mi	20 mins
92614	92843	11.8 mi	18 mins
92614	92844	12.3 mi	17 mins
92614	92845	15.6 mi	18 mins
92614	92846	16.1 mi	19 mins
92614	92850	16.3 mi	19 mins
92614	92856	11.6 mi	15 mins
92614	92857	14.3 mi	18 mins
92614	92859	11.0 mi	14 mins
92614	92861	13.2 mi	17 mins
92614	92862	15.5 mi	39 mins
92614	92863	12.1 mi	15 mins
92614	92864	11.6 mi	14 mins
92614	92865	14.7 mi	20 mins
92614	92866	11.5 mi	15 mins
92614	92867	13.0 mi	17 mins
92614	92868	12.9 mi	16 mins
92614	92869	11.7 mi	15 mins
92614	92870	18.4 mi	24 mins
92614	92871	20.1 mi	24 mins
92614	92877	28.1 mi	32 mins
92614	92878	24.7 mi	27 mins
92614	92879	27.4 mi	33 mins
92614	92881	29.3 mi	33 mins
92614	92882	28.6 mi	37 mins
92614	92883	36.1 mi	40 mins
92614	92885	20.1 mi	23 mins
92614	92886	22.0 mi	26 mins
92614	92887	20.8 mi	26 mins
92614	92899	16.8 mi	20 mins
92614	90680	15.2 mi	20 mins
92614	90740	17.0 mi	21 mins
92614	90742	15.1 mi	22 mins
92614	90743	19.3 mi	24 mins
92614	92028	67.2 mi	1 hour 11 mins
92614	92049	48.2 mi	46 mins
92614	92051	48.2 mi	46 mins
92614	92052	48.2 mi	46 mins
92614	92054	49.6 mi	47 mins
92614	92055	55.9 mi	1 hour 29 mins
92614	92057	56.6 mi	57 mins
92614	92058	61.7 mi	1 hour 15 mins
92614	92068	57.1 mi	57 mins
92614	92088	66.2 mi	1 hour 8 mins
92614	92530	44.8 mi	53 mins
92614	92531	47.3 mi	49 mins
92614	92532	51.0 mi	52 mins
92614	92562	65.1 mi	1 hour 12 mins
92614	92564	59.8 mi	1 hour 1 min
92614	92590	67.6 mi	1 hour 11 mins
92614	92595	52.9 mi	53 mins
92614	92602	8.5 mi	13 mins
92614	92603	7.1 mi	10 mins
92614	92604	3.3 mi	7 mins
92614	92605	11.5 mi	14 mins
92614	92606	1.5 mi	3 mins
92614	92607	18.7 mi	23 mins
92614	92609	12.0 mi	16 mins
92614	92610	16.0 mi	19 mins
92614	92612	3.4 mi	7 mins
92614	92614	1 ft	1 min
92614	92615	9.9 mi	15 mins
92614	92616	3.5 mi	7 mins
92614	92617	4.5 mi	10 mins
92614	92618	7.3 mi	14 mins
92614	92619	5.0 mi	10 mins
92614	92620	5.7 mi	11 mins
92614	92623	0.2 mi	1 min
92614	92624	23.8 mi	25 mins
92614	92625	7.0 mi	12 mins
92614	92626	5.3 mi	8 mins
92614	92627	7.2 mi	9 mins
92614	92628	6.5 mi	10 mins
92614	92629	24.4 mi	27 mins
92614	92630	11.9 mi	16 mins
92614	92637	10.0 mi	13 mins
92614	92646	9.8 mi	15 mins
92614	92647	12.2 mi	15 mins
92614	92648	13.2 mi	20 mins
92614	92649	13.9 mi	20 mins
92614	92650	5.9 mi	13 mins
92614	92651	13.7 mi	18 mins
92614	92652	14.3 mi	19 mins
92614	92653	12.9 mi	15 mins
92614	92654	10.9 mi	13 mins
92614	92655	11.7 mi	15 mins
92614	92656	13.8 mi	18 mins
92614	92657	8.4 mi	13 mins
92614	92658	4.3 mi	8 mins
92614	92659	8.3 mi	13 mins
92614	92660	5.1 mi	9 mins
92614	92661	10.4 mi	16 mins
92614	92662	6.7 mi	12 mins
92614	92663	9.2 mi	14 mins
92614	92672	30.7 mi	30 mins
92614	92673	25.5 mi	28 mins
92614	92674	25.6 mi	25 mins
92614	92675	26.5 mi	33 mins
92614	92676	18.0 mi	23 mins
92614	92677	18.3 mi	22 mins
92614	92678	23.4 mi	26 mins
92614	92679	22.3 mi	31 mins
92614	92683	12.9 mi	15 mins
92614	92684	11.0 mi	14 mins
92614	92685	12.0 mi	15 mins
92614	92688	17.6 mi	23 mins
92614	92690	16.7 mi	19 mins
92614	92691	13.6 mi	17 mins
92614	92692	15.7 mi	19 mins
92614	92693	20.1 mi	22 mins
92614	92694	18.6 mi	22 mins
92614	92697	3.5 mi	8 mins
92614	92698	10.8 mi	16 mins
92614	92701	6.2 mi	14 mins
92614	92702	6.4 mi	15 mins
92614	92703	9.7 mi	16 mins
92614	92704	7.2 mi	11 mins
92614	92705	7.4 mi	13 mins
92614	92706	10.4 mi	16 mins
92614	92707	3.9 mi	9 mins
92614	92708	8.7 mi	11 mins
92614	92711	9.0 mi	12 mins
92614	92712	6.6 mi	16 mins
92614	92728	8.0 mi	11 mins
92614	92735	3.6 mi	8 mins
92614	92780	6.0 mi	9 mins
92614	92781	8.1 mi	11 mins
92614	92782	5.3 mi	10 mins
92614	92799	5.3 mi	8 mins
92614	92801	17.4 mi	20 mins
92614	92802	14.7 mi	20 mins
92614	92803	16.2 mi	20 mins
92614	92804	18.5 mi	24 mins
92614	92805	15.1 mi	20 mins
92614	92806	14.9 mi	18 mins
92614	92807	16.1 mi	20 mins
92614	92808	21.4 mi	31 mins
92614	92809	18.1 mi	20 mins
92614	92811	16.5 mi	21 mins
92614	92812	14.9 mi	20 mins

Hope it is helpful to you.

NOTE:
I'm attaching a new zip with updated module that includes the procedure for Lucy and the imported xls file

 

[crowegreg]

Hello, You are correct. The posting of mine a few years ago is exactly what you are trying to do. If you haven't found a solution yet, I can zip my DB, and post it for you.
I developed this for a customer a few years ago, and haven't done any work with it since then.

 

[LucyK]

Thank you So Much!
this is wonderful and works really well. Exactly what I need it.

 

[jdraw]

Happy to help.

 

[LucyK]

Hi Jack,

Sorry to bother you, but I got an error on the Access DB.
I was getting the distance info for 2,700 records (on my excel file). It was working fine until record 2,200 when I started getting the error (see attached).

Is it possible that I "exceeded the limit with Google maps"? How can I fix this?

Thank you so much

Lucy

 

[jdraw]

I'm confused with what you are showing. I created a procedure using your excel data imported into Access. There is no need to use the sample form I used for the demo.
The distance/time from 92614 to 92660 is listed in the results in post #53. (5.1 mi/9 min)

If the sample data you provided was not your entire data set, and you ran a few thousand calls, then Google maps may have a limit on the number of calls per session.

Note: Just saw this in post 34
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.

 

[LucyK]

Hi,

Yes, I was trying to get another set with a new excel file with more zip codes. I was using the module you sent. The graphic I attached on my last post was to show even when I use the form you created, I get the same error.

it is the limits to 2,500 map loads (as per post #34)
Marv was also asking if there is a way to change the code in order to get the 25,000 loads per day vs the "Styled maps" with limit to 2,500? I don't see any replies regarding that.

Thank you

Lucy

 

[jdraw]

I found this link https://developers.google.com/maps/documentation/distancematrix/

that may have more info, or from which you may get additional info.
It does mention a 2500 limit within 24 hour period.

If you have more data or need it on a regular basis you may need an account????

You can get an approximate distance using Great Circle formula.

Let us know how you resolved this.