[AccessD] Calculating mileage

Thanks Jim.

John W. Colby
www.ColbyConsulting.com

On 9/28/2011 8:38 AM, Jim Dettman wrote:
> John,
>
>   Check out the following two links:
>
> http://rdsrc.us/7mwAve
>
>   This has a solution from Helen Feddema, which places a Google Map in a web
> browser control and allow you to get the route and mileage between zips.
> This is not exactly what you want, but it shows you the basic technique of
> formatting a URL for Google Maps.
>
> http://rdsrc.us/XqHekz
>
>   This second one is a lot closer to what you want, but it was done in an
> Excel spreadsheet.  Still VBA code though and you should be able to drop it
> into Access except for the cell references.  Scroll to the bottom and about
> four comments back.  Look for the one from Patrick with the download
> web-Google-maps-distances-10-2-.xls.
>
>    He notes that this is for distance between zips, but that an address in
> the to/from column would work as well, so that should suite your purposes.
>
>    There may be a solution that's dead on, but I don't have the time to
> search at the moment.  Between the two above, you should be able to figure
> it out fast enough.
>
> Jim.
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Tuesday, September 27, 2011 07:53 AM
> To: Access Developers discussion and problem solving
> Subject: [AccessD] Calculating mileage
>
> Before tax time I need to add a module to my inmate checkout to calculate
> mileage for all legs of a
> trip with the inmates.  The database contains records of where we go - the
> midpoints points - with
> addresses, but the database does not have my start / end point (home).
>
> 1) Home to prison
> 2) Prison to first destination in the db
> 3) First destination to second destination (if any)
> 4) Second destination to third destination (if any)
> 5) Last destination to prison
> 6) Prison to home
>
> I thought I would try to use Microsoft Streets and trips to do the mapping,
> though I am open to
> using Google Maps if there is a programming interface available to the VBA
> language.
>
> I could of course manually enter the trips into Streets and trips, get the
> mileage and build a table
> of all the "legs" that I have ever done but that is error prone and forces
> me to do the whole thing
> manually.  Also this is in use by other people so it would be nice to build
> a module that would also
> calculate their legs, which I certainly do not want to do myself manually.
>
> If for tax purposes I just assume that we always start from home and end at
> home I could use
> something like Streets and trips (which I have) to calculate all of these
> legs for each trip and
> place them in a table.  At tax time I then have a mileage log for the IRS.
>
> I have never used Streets and Trips for this purpose.  I do know that it has
> an API and VBA
> available for it.  If anyone uses it for this purpose and has a library (or
> a function) written to
> do this that they would share that would be great.  Or a web or help
> resource for this.
>
> Thanks,
>