How can I use this complex function with a query

[efandango]

I don't know how to implement a function, or refer to one formsa query. I
have a table with Lat/Long coordinates for a number of locations and want to
use the function to compare one master Lat/Long against the list of other
addresses and get it to deliver any address within a 3 mile radius.

this following function from microsoft uses the Great Arc Circle which will
return the shorted distance between two points on the globe


Function GreatArcDistance(Lat1 As Double, Lon1 As Double, Lat2 As Double,
Lon2 As Double, Radius As Double) As Double
'
' Calculates the Great Arc (shortest) distance between 2 locations on the
globe.
'
' Uses functions from Trigonometry
'
Dim X1 As Double, Y1 As Double, Z1 As Double, X2 As Double, Y2 As Double, Z2
As Double
Dim CosX As Double, ChordLen As Double
LatLongToXYZ Lat1, Lon1, Radius, X1, Y1, Z1
LatLongToXYZ Lat2, Lon2, Radius, X2, Y2, Z2
ChordLen = Sqr((X1 - X2) * (X1 - X2) + (Y1 - Y2) * (Y1 - Y2) + (Z1 - Z2) *
(Z1 - Z2))
CosX = 1 - ChordLen * ChordLen / (2 * Radius * Radius)
Debug.Print X1, Y1, Z1
Debug.Print X2, Y2, Z2
Debug.Print ChordLen, CosX
If CosX = 1 Or CosX = -1 Then
GreatArcDistance = 0
Else
GreatArcDistance = Sqr(1 - CosX * CosX) * Radius * PI() / 2
End If
End Function

 

[pietlinden]

I don't know how to implement a function, or refer to one formsa query. I
have a table with Lat/Long coordinates for a number of locations and want to
use the function to compare one master Lat/Long against the list of other
addresses and get it to deliver any address within a 3 mile radius.

this following function from microsoft uses the Great Arc Circle which will
return the shorted distance between two points on the globe

Function GreatArcDistance(Lat1 As Double, Lon1 As Double, Lat2 As Double,
Lon2 As Double, Radius As Double) As Double
'
' Calculates the Great Arc (shortest) distance between 2 locations on the
globe.
'
' Uses functions from Trigonometry
'
Dim X1 As Double, Y1 As Double, Z1 As Double, X2 As Double, Y2 As Double, Z2
As Double
Dim CosX As Double, ChordLen As Double
LatLongToXYZ Lat1, Lon1, Radius, X1, Y1, Z1
LatLongToXYZ Lat2, Lon2, Radius, X2, Y2, Z2
ChordLen = Sqr((X1 - X2) * (X1 - X2) + (Y1 - Y2) * (Y1 - Y2) + (Z1 - Z2) *
(Z1 - Z2))
CosX = 1 - ChordLen * ChordLen / (2 * Radius * Radius)
Debug.Print X1, Y1, Z1
Debug.Print X2, Y2, Z2
Debug.Print ChordLen, CosX
If CosX = 1 Or CosX = -1 Then
GreatArcDistance = 0
Else
GreatArcDistance = Sqr(1 - CosX * CosX) * Radius * PI() / 2
End If
End Function

SELECT Lat1, Lon1, Lat2, Lon2, lngRadius,
MyFunctionResult:GreatArcDistance(Lat1,Lon1,Lat2,Lon2,lngRadius)
FROM MyTable...

 

[Douglas J. Steele]

Slight syntax error:

SELECT Lat1, Lon1, Lat2, Lon2, lngRadius,
GreatArcDistance(Lat1,Lon1,Lat2,Lon2,lngRadius) AS MyFunctionResult
FROM MyTable

Given how the function was written, it's critical that Lat1, Lon1, Lat2,
Lon2 and lngRadius all be defined: the function will fail if any of them are
Null. Therefore, it might be bettter to use

SELECT Lat1, Lon1, Lat2, Lon2, lngRadius,
GreatArcDistance(Lat1,Lon1,Lat2,Lon2,lngRadius) AS MyFunctionResult
FROM MyTable
WHERE Lat1 IS NOT NULL
AND Lon1 IS NOT NULL
AND Lat2 IS NOT NULL
AND Lon2 IS NOT NULL
AND lngRadius IS NOT NULL

 

[John Spencer]

Ok. I'm ignorant.

I understand what Lat1, Lon1, Lat2, Lon2 stand for. But how are they
expressed as doubles? And what is lngRadius?

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Douglas J. Steele]

To be honest, I didn't bother trying to figure the function out. Like
Pieter, I just answered the literal question. However, in response to your
question, I just did a quick Google, and got a couple of good hits.
http://en.wikipedia.org/wiki/Great-circle_distance explains why you need to
know the radius: you need to include the curvature of the earth when
computing the distance between two points.

Doubles does seem unnecessary. I suppose lngRadius could be defined as a
constant within the function, but I suspect the writer of the function
thought it could be useful for other planets as well.

See, too,
http://help.wugnet.com/office2/Calculating-distances-ftopict807712.html

 

[John Spencer]

Never mind. I figured it out with some help from google.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[efandango]

Thanks for help answer this question, I think I understand the SQL that you
provided. But don't know how the function works from a VBA perspective.
Where/How do I store the 'GreatArcDistance'. I'm a newbie when it comes to
custom functions and modules and would appreciate some help/guidance on how
to implemen the function within Access.

 

[John W. Vinson]

Where/How do I store the 'GreatArcDistance'.

Generally, unless the calculation is demonstrably and unreasonably slow, You
Don't.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

If you feel that you do need to store it, add a GreatCircleDistance field to
your table, and run an Update query updating it to the function expression.

John W. Vinson [MVP]

 

[efandango]

John,

what you say about not storing the calculated data (that was never my
intention)
makes perfect sense. I suppose it's my lack of knowledge that led to this
misunderstanding. What I meant was how/where do I declare the Microsoft
funtion that I pasted into the original thread. How do the actual 'mechanics'
of what I'm trying to acheive fit together. I understand my own concept;
which is to throw a bunch of Lat/Lon coordinates at a query using a
calculated field to derive proximity to a target Lat/Lon and have that query
supply a subset of answers to a form. I just don't quite know how to go about
building the process; in particular how to reference that large block of
'Great Arc ' code. which I seemingly cannot fit into a single calculated
control. So what goes where?, so to speak. (this is really what I meant in my
previous post)

 

[John W. Vinson]

I just don't quite know how to go about
building the process; in particular how to reference that large block of
'Great Arc ' code. which I seemingly cannot fit into a single calculated
control. So what goes where?, so to speak. (this is really what I meant in my
previous post)

Pardon my misinterpretation!

Stepwise:

Select the Modules tab in your database window. Create a new Module.

Copy and paste the VBA code into the module. Save it (click the diskette
icon), but save it with a *DIFFERENT* name than the name of the function -
basLatLong let's say.

On the menu select Debug... Compile <your database>. Fix any compile errors.

In the Query, simply type

Dist: GreatArcDistance([Lat1], [Lon1], [Lat2], [Lon2], 3963.)

is you want to use the equatorial radius of the Earth in miles, and if there
are fields in your query named Lat1, Lon1, Lat2 and Lon2 - replace these with
the actual fieldnames if not.


John W. Vinson [MVP]

 

[efandango]

John,

thank you for coming back to me on this. Your steps make sense to me, but
have highlighted a potential problem with the great arc formula itself
relative to what I am trying to do: It is essentialy comparing one set of
coordinates with another set and giving the distance, (right?), what I want
to do is have a single master Lat/Lon and compare ALL the other seperate
Lat/Lons. The master Lat/Lon will be on a form's text boxes, but the compared
Lat/Lons will be in the table. (In plain english, I'm trying to find all the
addresses within a given radius relative to a nominated address and show them
on a form, via a query.)

So my question is; How is the best way to adjust the SQL, (do I even need
to?) should I reference the Lat1 & Lon1 like Forms!Masterform!.[Lat] (and the
same for the [Lon1] control or is there a simpler, less disk/cpu intensive
method. (I hope I'm making sense, as I'm still finding my way with Access &
VBA)

I just don't quite know how to go about
building the process; in particular how to reference that large block of
'Great Arc ' code. which I seemingly cannot fit into a single calculated
control. So what goes where?, so to speak. (this is really what I meant in my
previous post)

Pardon my misinterpretation!

Stepwise:

Select the Modules tab in your database window. Create a new Module.

Copy and paste the VBA code into the module. Save it (click the diskette
icon), but save it with a *DIFFERENT* name than the name of the function -
basLatLong let's say.

On the menu select Debug... Compile <your database>. Fix any compile errors.

In the Query, simply type

Dist: GreatArcDistance([Lat1], [Lon1], [Lat2], [Lon2], 3963.)

is you want to use the equatorial radius of the Earth in miles, and if there
are fields in your query named Lat1, Lon1, Lat2 and Lon2 - replace these with
the actual fieldnames if not.


John W. Vinson [MVP]

 

[efandango]

John,

When I try to sun the query, I get the following error:
Ambigous name, in query expression
'GreatArcDistance([Lat1],[Lon1],[Lat2,[Lon2],3963'

This is the SQL

*****************
SELECT [#Sample KML].name, [#Sample KML].address, [#Sample KML].coordinates,
Mid([coordinates],InStr([coordinates],",")+1,Len([coordinates])-InStr([coordinates],",")-2)
AS Lat2, "-" &
Mid([coordinates],InStr([coordinates],"-")+1,Len([coordinates])-InStr([coordinates],",")-3)
AS Lon2, [#Sample KML].Lat1, [#Sample KML].Lon1, [#Sample KML].[Lat-Lon
Name], GreatArcDistance([Lat1],[Lon1],[Lat2],[Lon2],3963) AS Dist
FROM [#Sample KML];

*****************



This is the module code: (named as basLatLong)

*****************
Option Compare Database

Function GreatArcDistance(Lat1 As Double, Lon1 As Double, Lat2 As Double,
Lon2 As Double, Radius As Double) As Double
'
' Calculates the Great Arc (shortest) distance between 2 locations on the
globe.
'
' Uses functions from Trigonometry
'
Dim X1 As Double, Y1 As Double, Z1 As Double, X2 As Double, Y2 As Double, Z2
As Double
Dim CosX As Double, ChordLen As Double
LatLongToXYZ Lat1, Lon1, Radius, X1, Y1, Z1
LatLongToXYZ Lat2, Lon2, Radius, X2, Y2, Z2
ChordLen = Sqr((X1 - X2) * (X1 - X2) + (Y1 - Y2) * (Y1 - Y2) + (Z1 - Z2) *
(Z1 - Z2))
CosX = 1 - ChordLen * ChordLen / (2 * Radius * Radius)
Debug.Print X1, Y1, Z1
Debug.Print X2, Y2, Z2
Debug.Print ChordLen, CosX
If CosX = 1 Or CosX = -1 Then
GreatArcDistance = 0
Else
GreatArcDistance = Sqr(1 - CosX * CosX) * Radius * PI() / 2
End If
End Function
*****************

I just don't quite know how to go about
building the process; in particular how to reference that large block of
'Great Arc ' code. which I seemingly cannot fit into a single calculated
control. So what goes where?, so to speak. (this is really what I meant in my
previous post)

Pardon my misinterpretation!

Stepwise:

Select the Modules tab in your database window. Create a new Module.

Copy and paste the VBA code into the module. Save it (click the diskette
icon), but save it with a *DIFFERENT* name than the name of the function -
basLatLong let's say.

On the menu select Debug... Compile <your database>. Fix any compile errors.

In the Query, simply type

Dist: GreatArcDistance([Lat1], [Lon1], [Lat2], [Lon2], 3963.)

is you want to use the equatorial radius of the Earth in miles, and if there
are fields in your query named Lat1, Lon1, Lat2 and Lon2 - replace these with
the actual fieldnames if not.


John W. Vinson [MVP]

 

[Douglas J. Steele]

What are the names of the fields in #SampleKML?

The error implies that Access thinks something in that function call
([Lat1], [Lon1], [Lat2] or [Lon2]) occurs more than once in #SampleKML.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

John,

When I try to sun the query, I get the following error:
Ambigous name, in query expression
'GreatArcDistance([Lat1],[Lon1],[Lat2,[Lon2],3963'

This is the SQL

*****************
SELECT [#Sample KML].name, [#Sample KML].address, [#Sample
KML].coordinates,
Mid([coordinates],InStr([coordinates],",")+1,Len([coordinates])-InStr([coordinates],",")-2)
AS Lat2, "-" &
Mid([coordinates],InStr([coordinates],"-")+1,Len([coordinates])-InStr([coordinates],",")-3)
AS Lon2, [#Sample KML].Lat1, [#Sample KML].Lon1, [#Sample KML].[Lat-Lon
Name], GreatArcDistance([Lat1],[Lon1],[Lat2],[Lon2],3963) AS Dist
FROM [#Sample KML];

*****************



This is the module code: (named as basLatLong)

*****************
Option Compare Database

Function GreatArcDistance(Lat1 As Double, Lon1 As Double, Lat2 As Double,
Lon2 As Double, Radius As Double) As Double
'
' Calculates the Great Arc (shortest) distance between 2 locations on the
globe.
'
' Uses functions from Trigonometry
'
Dim X1 As Double, Y1 As Double, Z1 As Double, X2 As Double, Y2 As Double,
Z2
As Double
Dim CosX As Double, ChordLen As Double
LatLongToXYZ Lat1, Lon1, Radius, X1, Y1, Z1
LatLongToXYZ Lat2, Lon2, Radius, X2, Y2, Z2
ChordLen = Sqr((X1 - X2) * (X1 - X2) + (Y1 - Y2) * (Y1 - Y2) + (Z1 - Z2)
*
(Z1 - Z2))
CosX = 1 - ChordLen * ChordLen / (2 * Radius * Radius)
Debug.Print X1, Y1, Z1
Debug.Print X2, Y2, Z2
Debug.Print ChordLen, CosX
If CosX = 1 Or CosX = -1 Then
GreatArcDistance = 0
Else
GreatArcDistance = Sqr(1 - CosX * CosX) * Radius * PI() / 2
End If
End Function
*****************

I just don't quite know how to go about
building the process; in particular how to reference that large block of
'Great Arc ' code. which I seemingly cannot fit into a single calculated
control. So what goes where?, so to speak. (this is really what I meant
in my
previous post)

Pardon my misinterpretation!

Stepwise:

Select the Modules tab in your database window. Create a new Module.

Copy and paste the VBA code into the module. Save it (click the diskette
icon), but save it with a *DIFFERENT* name than the name of the
function -
basLatLong let's say.

On the menu select Debug... Compile <your database>. Fix any compile
errors.

In the Query, simply type

Dist: GreatArcDistance([Lat1], [Lon1], [Lat2], [Lon2], 3963.)

is you want to use the equatorial radius of the Earth in miles, and if
there
are fields in your query named Lat1, Lon1, Lat2 and Lon2 - replace these
with
the actual fieldnames if not.


John W. Vinson [MVP]

 

[efandango]

The names (datatype) are as follows:

ID Autonumber
name Text
address Text
coordinates Text
Lat1 Text
Lon1 Text
Lat-Lon Name Text
Radius Text

What are the names of the fields in #SampleKML?

The error implies that Access thinks something in that function call
([Lat1], [Lon1], [Lat2] or [Lon2]) occurs more than once in #SampleKML.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

John,

When I try to sun the query, I get the following error:
Ambigous name, in query expression
'GreatArcDistance([Lat1],[Lon1],[Lat2,[Lon2],3963'

This is the SQL

*****************
SELECT [#Sample KML].name, [#Sample KML].address, [#Sample
KML].coordinates,
Mid([coordinates],InStr([coordinates],",")+1,Len([coordinates])-InStr([coordinates],",")-2)
AS Lat2, "-" &
Mid([coordinates],InStr([coordinates],"-")+1,Len([coordinates])-InStr([coordinates],",")-3)
AS Lon2, [#Sample KML].Lat1, [#Sample KML].Lon1, [#Sample KML].[Lat-Lon
Name], GreatArcDistance([Lat1],[Lon1],[Lat2],[Lon2],3963) AS Dist
FROM [#Sample KML];

*****************



This is the module code: (named as basLatLong)

*****************
Option Compare Database

Function GreatArcDistance(Lat1 As Double, Lon1 As Double, Lat2 As Double,
Lon2 As Double, Radius As Double) As Double
'
' Calculates the Great Arc (shortest) distance between 2 locations on the
globe.
'
' Uses functions from Trigonometry
'
Dim X1 As Double, Y1 As Double, Z1 As Double, X2 As Double, Y2 As Double,
Z2
As Double
Dim CosX As Double, ChordLen As Double
LatLongToXYZ Lat1, Lon1, Radius, X1, Y1, Z1
LatLongToXYZ Lat2, Lon2, Radius, X2, Y2, Z2
ChordLen = Sqr((X1 - X2) * (X1 - X2) + (Y1 - Y2) * (Y1 - Y2) + (Z1 - Z2)
*
(Z1 - Z2))
CosX = 1 - ChordLen * ChordLen / (2 * Radius * Radius)
Debug.Print X1, Y1, Z1
Debug.Print X2, Y2, Z2
Debug.Print ChordLen, CosX
If CosX = 1 Or CosX = -1 Then
GreatArcDistance = 0
Else
GreatArcDistance = Sqr(1 - CosX * CosX) * Radius * PI() / 2
End If
End Function
*****************

On Thu, 25 Oct 2007 14:51:01 -0700, efandango

I just don't quite know how to go about
building the process; in particular how to reference that large block of
'Great Arc ' code. which I seemingly cannot fit into a single calculated
control. So what goes where?, so to speak. (this is really what I meant
in my
previous post)

Pardon my misinterpretation!

Stepwise:

Select the Modules tab in your database window. Create a new Module.

Copy and paste the VBA code into the module. Save it (click the diskette
icon), but save it with a *DIFFERENT* name than the name of the
function -
basLatLong let's say.

On the menu select Debug... Compile <your database>. Fix any compile
errors.

In the Query, simply type

Dist: GreatArcDistance([Lat1], [Lon1], [Lat2], [Lon2], 3963.)

is you want to use the equatorial radius of the Earth in miles, and if
there
are fields in your query named Lat1, Lon1, Lat2 and Lon2 - replace these
with
the actual fieldnames if not.


John W. Vinson [MVP]

 

[Douglas J. Steele]

Since Lat2 and Lon2 are computed values, you may have to repeat the
calculation:

SELECT [#Sample KML].name, [#Sample KML].address, [#Sample KML].coordinates,
Mid([coordinates],InStr([coordinates],",")+1,Len([coordinates])-InStr([coordinates],",")-2)
AS Lat2, "-" &
Mid([coordinates],InStr([coordinates],"-")+1,Len([coordinates])-InStr([coordinates],",")-3)
AS Lon2, [#Sample KML].Lat1, [#Sample KML].Lon1, [#Sample KML].[Lat-Lon
Name],
GreatArcDistance([Lat1],[Lon1],Mid([coordinates],InStr([coordinates],",")+1,Len([coordinates])-InStr([coordinates],",")-2)
,Mid([coordinates],InStr([coordinates],"-")+1,Len([coordinates])-InStr([coordinates],",")-3)
,3963) AS Dist
FROM [#Sample KML];

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

The names (datatype) are as follows:

ID Autonumber
name Text
address Text
coordinates Text
Lat1 Text
Lon1 Text
Lat-Lon Name Text
Radius Text

What are the names of the fields in #SampleKML?

The error implies that Access thinks something in that function call
([Lat1], [Lon1], [Lat2] or [Lon2]) occurs more than once in #SampleKML.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

John,

When I try to sun the query, I get the following error:
Ambigous name, in query expression
'GreatArcDistance([Lat1],[Lon1],[Lat2,[Lon2],3963'

This is the SQL

*****************
SELECT [#Sample KML].name, [#Sample KML].address, [#Sample
KML].coordinates,
Mid([coordinates],InStr([coordinates],",")+1,Len([coordinates])-InStr([coordinates],",")-2)
AS Lat2, "-" &
Mid([coordinates],InStr([coordinates],"-")+1,Len([coordinates])-InStr([coordinates],",")-3)
AS Lon2, [#Sample KML].Lat1, [#Sample KML].Lon1, [#Sample KML].[Lat-Lon
Name], GreatArcDistance([Lat1],[Lon1],[Lat2],[Lon2],3963) AS Dist
FROM [#Sample KML];

*****************



This is the module code: (named as basLatLong)

*****************
Option Compare Database

Function GreatArcDistance(Lat1 As Double, Lon1 As Double, Lat2 As
Double,
Lon2 As Double, Radius As Double) As Double
'
' Calculates the Great Arc (shortest) distance between 2 locations on
the
globe.
'
' Uses functions from Trigonometry
'
Dim X1 As Double, Y1 As Double, Z1 As Double, X2 As Double, Y2 As
Double,
Z2
As Double
Dim CosX As Double, ChordLen As Double
LatLongToXYZ Lat1, Lon1, Radius, X1, Y1, Z1
LatLongToXYZ Lat2, Lon2, Radius, X2, Y2, Z2
ChordLen = Sqr((X1 - X2) * (X1 - X2) + (Y1 - Y2) * (Y1 - Y2) + (Z1 -
Z2)
*
(Z1 - Z2))
CosX = 1 - ChordLen * ChordLen / (2 * Radius * Radius)
Debug.Print X1, Y1, Z1
Debug.Print X2, Y2, Z2
Debug.Print ChordLen, CosX
If CosX = 1 Or CosX = -1 Then
GreatArcDistance = 0
Else
GreatArcDistance = Sqr(1 - CosX * CosX) * Radius * PI() / 2
End If
End Function
*****************

:

On Thu, 25 Oct 2007 14:51:01 -0700, efandango

I just don't quite know how to go about
building the process; in particular how to reference that large block
of
'Great Arc ' code. which I seemingly cannot fit into a single
calculated
control. So what goes where?, so to speak. (this is really what I
meant
in my
previous post)

Pardon my misinterpretation!

Stepwise:

Select the Modules tab in your database window. Create a new Module.

Copy and paste the VBA code into the module. Save it (click the
diskette
icon), but save it with a *DIFFERENT* name than the name of the
function -
basLatLong let's say.

On the menu select Debug... Compile <your database>. Fix any compile
errors.

In the Query, simply type

Dist: GreatArcDistance([Lat1], [Lon1], [Lat2], [Lon2], 3963.)

is you want to use the equatorial radius of the Earth in miles, and if
there
are fields in your query named Lat1, Lon1, Lat2 and Lon2 - replace
these
with
the actual fieldnames if not.


John W. Vinson [MVP]

 

[efandango]

Douglas,

I pasted your SQL code in but still get the same error message:

Ambigous name, in query expression
'GreatArcDistance([Lat1],[Lon1],[Lat2,[Lon2],3963'...

do you think, perhaps there is something wrong with the microsoft GreatArc
code, itself? I'm baffled...

Since Lat2 and Lon2 are computed values, you may have to repeat the
calculation:

SELECT [#Sample KML].name, [#Sample KML].address, [#Sample KML].coordinates,
Mid([coordinates],InStr([coordinates],",")+1,Len([coordinates])-InStr([coordinates],",")-2)
AS Lat2, "-" &
Mid([coordinates],InStr([coordinates],"-")+1,Len([coordinates])-InStr([coordinates],",")-3)
AS Lon2, [#Sample KML].Lat1, [#Sample KML].Lon1, [#Sample KML].[Lat-Lon
Name],
GreatArcDistance([Lat1],[Lon1],Mid([coordinates],InStr([coordinates],",")+1,Len([coordinates])-InStr([coordinates],",")-2)
,Mid([coordinates],InStr([coordinates],"-")+1,Len([coordinates])-InStr([coordinates],",")-3)
,3963) AS Dist
FROM [#Sample KML];

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

The names (datatype) are as follows:

ID Autonumber
name Text
address Text
coordinates Text
Lat1 Text
Lon1 Text
Lat-Lon Name Text
Radius Text

What are the names of the fields in #SampleKML?

The error implies that Access thinks something in that function call
([Lat1], [Lon1], [Lat2] or [Lon2]) occurs more than once in #SampleKML.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


John,

When I try to sun the query, I get the following error:
Ambigous name, in query expression
'GreatArcDistance([Lat1],[Lon1],[Lat2,[Lon2],3963'

This is the SQL

*****************
SELECT [#Sample KML].name, [#Sample KML].address, [#Sample
KML].coordinates,
Mid([coordinates],InStr([coordinates],",")+1,Len([coordinates])-InStr([coordinates],",")-2)
AS Lat2, "-" &
Mid([coordinates],InStr([coordinates],"-")+1,Len([coordinates])-InStr([coordinates],",")-3)
AS Lon2, [#Sample KML].Lat1, [#Sample KML].Lon1, [#Sample KML].[Lat-Lon
Name], GreatArcDistance([Lat1],[Lon1],[Lat2],[Lon2],3963) AS Dist
FROM [#Sample KML];

*****************



This is the module code: (named as basLatLong)

*****************
Option Compare Database

Function GreatArcDistance(Lat1 As Double, Lon1 As Double, Lat2 As
Double,
Lon2 As Double, Radius As Double) As Double
'
' Calculates the Great Arc (shortest) distance between 2 locations on
the
globe.
'
' Uses functions from Trigonometry
'
Dim X1 As Double, Y1 As Double, Z1 As Double, X2 As Double, Y2 As
Double,
Z2
As Double
Dim CosX As Double, ChordLen As Double
LatLongToXYZ Lat1, Lon1, Radius, X1, Y1, Z1
LatLongToXYZ Lat2, Lon2, Radius, X2, Y2, Z2
ChordLen = Sqr((X1 - X2) * (X1 - X2) + (Y1 - Y2) * (Y1 - Y2) + (Z1 -
Z2)
*
(Z1 - Z2))
CosX = 1 - ChordLen * ChordLen / (2 * Radius * Radius)
Debug.Print X1, Y1, Z1
Debug.Print X2, Y2, Z2
Debug.Print ChordLen, CosX
If CosX = 1 Or CosX = -1 Then
GreatArcDistance = 0
Else
GreatArcDistance = Sqr(1 - CosX * CosX) * Radius * PI() / 2
End If
End Function
*****************

:

On Thu, 25 Oct 2007 14:51:01 -0700, efandango

I just don't quite know how to go about
building the process; in particular how to reference that large block
of
'Great Arc ' code. which I seemingly cannot fit into a single
calculated
control. So what goes where?, so to speak. (this is really what I
meant
in my
previous post)

Pardon my misinterpretation!

Stepwise:

Select the Modules tab in your database window. Create a new Module.

Copy and paste the VBA code into the module. Save it (click the
diskette
icon), but save it with a *DIFFERENT* name than the name of the
function -
basLatLong let's say.

On the menu select Debug... Compile <your database>. Fix any compile
errors.

In the Query, simply type

Dist: GreatArcDistance([Lat1], [Lon1], [Lat2], [Lon2], 3963.)

is you want to use the equatorial radius of the Earth in miles, and if
there
are fields in your query named Lat1, Lon1, Lat2 and Lon2 - replace
these
with
the actual fieldnames if not.


John W. Vinson [MVP]

 

[efandango]

Douglas,

I figured out the problem with the ambigous name, I googled around and
discovered other posts and solutions, whereby a guy had set up the function
under another module; I took a look at modules and discovered I did exaclty
the same thing, and deleted the offending module. However, now that my
module/function runs a bit further, I get this error message with the VBA
window:

Compile error:
Sub or function not defined; and it highlights this particular line:

LatLongToXYZ

within this larger line.
LatLongToXYZ Lat1, Lon1, Radius, X1, Y1, Z1

the full code:

*************************
Option Compare Database

Function GreatArcDistance(Lat1 As Double, Lon1 As Double, Lat2 As Double,
Lon2 As Double, Radius As Double) As Double
'
' Calculates the Great Arc (shortest) distance between 2 locations on the
globe.
'
' Uses functions from Trigonometry
'
Dim X1 As Double, Y1 As Double, Z1 As Double, X2 As Double, Y2 As Double, Z2
As Double
Dim CosX As Double, ChordLen As Double
LatLongToXYZ Lat1, Lon1, Radius, X1, Y1, Z1
LatLongToXYZ Lat2, Lon2, Radius, X2, Y2, Z2
ChordLen = Sqr((X1 - X2) * (X1 - X2) + (Y1 - Y2) * (Y1 - Y2) + (Z1 - Z2) *
(Z1 - Z2))
CosX = 1 - ChordLen * ChordLen / (2 * Radius * Radius)
Debug.Print X1, Y1, Z1
Debug.Print X2, Y2, Z2
Debug.Print ChordLen, CosX
If CosX = 1 Or CosX = -1 Then GreatArcDistance = 0
Else
GreatArcDistance = Sqr(1 - CosX * CosX) * Radius * PI() / 2
End If
End Function

*************************

Since Lat2 and Lon2 are computed values, you may have to repeat the
calculation:

SELECT [#Sample KML].name, [#Sample KML].address, [#Sample KML].coordinates,
Mid([coordinates],InStr([coordinates],",")+1,Len([coordinates])-InStr([coordinates],",")-2)
AS Lat2, "-" &
Mid([coordinates],InStr([coordinates],"-")+1,Len([coordinates])-InStr([coordinates],",")-3)
AS Lon2, [#Sample KML].Lat1, [#Sample KML].Lon1, [#Sample KML].[Lat-Lon
Name],
GreatArcDistance([Lat1],[Lon1],Mid([coordinates],InStr([coordinates],",")+1,Len([coordinates])-InStr([coordinates],",")-2)
,Mid([coordinates],InStr([coordinates],"-")+1,Len([coordinates])-InStr([coordinates],",")-3)
,3963) AS Dist
FROM [#Sample KML];

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

The names (datatype) are as follows:

ID Autonumber
name Text
address Text
coordinates Text
Lat1 Text
Lon1 Text
Lat-Lon Name Text
Radius Text

What are the names of the fields in #SampleKML?

The error implies that Access thinks something in that function call
([Lat1], [Lon1], [Lat2] or [Lon2]) occurs more than once in #SampleKML.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


John,

When I try to sun the query, I get the following error:
Ambigous name, in query expression
'GreatArcDistance([Lat1],[Lon1],[Lat2,[Lon2],3963'

This is the SQL

*****************
SELECT [#Sample KML].name, [#Sample KML].address, [#Sample
KML].coordinates,
Mid([coordinates],InStr([coordinates],",")+1,Len([coordinates])-InStr([coordinates],",")-2)
AS Lat2, "-" &
Mid([coordinates],InStr([coordinates],"-")+1,Len([coordinates])-InStr([coordinates],",")-3)
AS Lon2, [#Sample KML].Lat1, [#Sample KML].Lon1, [#Sample KML].[Lat-Lon
Name], GreatArcDistance([Lat1],[Lon1],[Lat2],[Lon2],3963) AS Dist
FROM [#Sample KML];

*****************



This is the module code: (named as basLatLong)

*****************
Option Compare Database

Function GreatArcDistance(Lat1 As Double, Lon1 As Double, Lat2 As
Double,
Lon2 As Double, Radius As Double) As Double
'
' Calculates the Great Arc (shortest) distance between 2 locations on
the
globe.
'
' Uses functions from Trigonometry
'
Dim X1 As Double, Y1 As Double, Z1 As Double, X2 As Double, Y2 As
Double,
Z2
As Double
Dim CosX As Double, ChordLen As Double
LatLongToXYZ Lat1, Lon1, Radius, X1, Y1, Z1
LatLongToXYZ Lat2, Lon2, Radius, X2, Y2, Z2
ChordLen = Sqr((X1 - X2) * (X1 - X2) + (Y1 - Y2) * (Y1 - Y2) + (Z1 -
Z2)
*
(Z1 - Z2))
CosX = 1 - ChordLen * ChordLen / (2 * Radius * Radius)
Debug.Print X1, Y1, Z1
Debug.Print X2, Y2, Z2
Debug.Print ChordLen, CosX
If CosX = 1 Or CosX = -1 Then
GreatArcDistance = 0
Else
GreatArcDistance = Sqr(1 - CosX * CosX) * Radius * PI() / 2
End If
End Function
*****************

:

On Thu, 25 Oct 2007 14:51:01 -0700, efandango

I just don't quite know how to go about
building the process; in particular how to reference that large block
of
'Great Arc ' code. which I seemingly cannot fit into a single
calculated
control. So what goes where?, so to speak. (this is really what I
meant
in my
previous post)

Pardon my misinterpretation!

Stepwise:

Select the Modules tab in your database window. Create a new Module.

Copy and paste the VBA code into the module. Save it (click the
diskette
icon), but save it with a *DIFFERENT* name than the name of the
function -
basLatLong let's say.

On the menu select Debug... Compile <your database>. Fix any compile
errors.

In the Query, simply type

Dist: GreatArcDistance([Lat1], [Lon1], [Lat2], [Lon2], 3963.)

is you want to use the equatorial radius of the Earth in miles, and if
there
are fields in your query named Lat1, Lon1, Lat2 and Lon2 - replace
these
with
the actual fieldnames if not.


John W. Vinson [MVP]

 

[Douglas J. Steele]

What is LatLongToXYZ? Is it defined? If it is, what's the name of the module
in which it exists? If the module is also named LatLongToXYZ, rename the
module: modules cannot have the same names as routines.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Douglas,

I figured out the problem with the ambigous name, I googled around and
discovered other posts and solutions, whereby a guy had set up the
function
under another module; I took a look at modules and discovered I did
exaclty
the same thing, and deleted the offending module. However, now that my
module/function runs a bit further, I get this error message with the VBA
window:

Compile error:
Sub or function not defined; and it highlights this particular line:

LatLongToXYZ

within this larger line.
LatLongToXYZ Lat1, Lon1, Radius, X1, Y1, Z1

the full code:

*************************
Option Compare Database

Function GreatArcDistance(Lat1 As Double, Lon1 As Double, Lat2 As Double,
Lon2 As Double, Radius As Double) As Double
'
' Calculates the Great Arc (shortest) distance between 2 locations on the
globe.
'
' Uses functions from Trigonometry
'
Dim X1 As Double, Y1 As Double, Z1 As Double, X2 As Double, Y2 As Double,
Z2
As Double
Dim CosX As Double, ChordLen As Double
LatLongToXYZ Lat1, Lon1, Radius, X1, Y1, Z1
LatLongToXYZ Lat2, Lon2, Radius, X2, Y2, Z2
ChordLen = Sqr((X1 - X2) * (X1 - X2) + (Y1 - Y2) * (Y1 - Y2) + (Z1 - Z2)
*
(Z1 - Z2))
CosX = 1 - ChordLen * ChordLen / (2 * Radius * Radius)
Debug.Print X1, Y1, Z1
Debug.Print X2, Y2, Z2
Debug.Print ChordLen, CosX
If CosX = 1 Or CosX = -1 Then GreatArcDistance = 0
Else
GreatArcDistance = Sqr(1 - CosX * CosX) * Radius * PI() / 2
End If
End Function

*************************

Since Lat2 and Lon2 are computed values, you may have to repeat the
calculation:

SELECT [#Sample KML].name, [#Sample KML].address, [#Sample
KML].coordinates,
Mid([coordinates],InStr([coordinates],",")+1,Len([coordinates])-InStr([coordinates],",")-2)
AS Lat2, "-" &
Mid([coordinates],InStr([coordinates],"-")+1,Len([coordinates])-InStr([coordinates],",")-3)
AS Lon2, [#Sample KML].Lat1, [#Sample KML].Lon1, [#Sample KML].[Lat-Lon
Name],
GreatArcDistance([Lat1],[Lon1],Mid([coordinates],InStr([coordinates],",")+1,Len([coordinates])-InStr([coordinates],",")-2)
,Mid([coordinates],InStr([coordinates],"-")+1,Len([coordinates])-InStr([coordinates],",")-3)
,3963) AS Dist
FROM [#Sample KML];

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

The names (datatype) are as follows:

ID Autonumber
name Text
address Text
coordinates Text
Lat1 Text
Lon1 Text
Lat-Lon Name Text
Radius Text




:

What are the names of the fields in #SampleKML?

The error implies that Access thinks something in that function call
([Lat1], [Lon1], [Lat2] or [Lon2]) occurs more than once in
#SampleKML.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


John,

When I try to sun the query, I get the following error:
Ambigous name, in query expression
'GreatArcDistance([Lat1],[Lon1],[Lat2,[Lon2],3963'

This is the SQL

*****************
SELECT [#Sample KML].name, [#Sample KML].address, [#Sample
KML].coordinates,
Mid([coordinates],InStr([coordinates],",")+1,Len([coordinates])-InStr([coordinates],",")-2)
AS Lat2, "-" &
Mid([coordinates],InStr([coordinates],"-")+1,Len([coordinates])-InStr([coordinates],",")-3)
AS Lon2, [#Sample KML].Lat1, [#Sample KML].Lon1, [#Sample
KML].[Lat-Lon
Name], GreatArcDistance([Lat1],[Lon1],[Lat2],[Lon2],3963) AS Dist
FROM [#Sample KML];

*****************



This is the module code: (named as basLatLong)

*****************
Option Compare Database

Function GreatArcDistance(Lat1 As Double, Lon1 As Double, Lat2 As
Double,
Lon2 As Double, Radius As Double) As Double
'
' Calculates the Great Arc (shortest) distance between 2 locations
on
the
globe.
'
' Uses functions from Trigonometry
'
Dim X1 As Double, Y1 As Double, Z1 As Double, X2 As Double, Y2 As
Double,
Z2
As Double
Dim CosX As Double, ChordLen As Double
LatLongToXYZ Lat1, Lon1, Radius, X1, Y1, Z1
LatLongToXYZ Lat2, Lon2, Radius, X2, Y2, Z2
ChordLen = Sqr((X1 - X2) * (X1 - X2) + (Y1 - Y2) * (Y1 - Y2) +
(Z1 -
Z2)
*
(Z1 - Z2))
CosX = 1 - ChordLen * ChordLen / (2 * Radius * Radius)
Debug.Print X1, Y1, Z1
Debug.Print X2, Y2, Z2
Debug.Print ChordLen, CosX
If CosX = 1 Or CosX = -1 Then
GreatArcDistance = 0
Else
GreatArcDistance = Sqr(1 - CosX * CosX) * Radius * PI() / 2
End If
End Function
*****************

:

On Thu, 25 Oct 2007 14:51:01 -0700, efandango

I just don't quite know how to go about
building the process; in particular how to reference that large
block
of
'Great Arc ' code. which I seemingly cannot fit into a single
calculated
control. So what goes where?, so to speak. (this is really what I
meant
in my
previous post)

Pardon my misinterpretation!

Stepwise:

Select the Modules tab in your database window. Create a new
Module.

Copy and paste the VBA code into the module. Save it (click the
diskette
icon), but save it with a *DIFFERENT* name than the name of the
function -
basLatLong let's say.

On the menu select Debug... Compile <your database>. Fix any
compile
errors.

In the Query, simply type

Dist: GreatArcDistance([Lat1], [Lon1], [Lat2], [Lon2], 3963.)

is you want to use the equatorial radius of the Earth in miles, and
if
there
are fields in your query named Lat1, Lon1, Lat2 and Lon2 - replace
these
with
the actual fieldnames if not.


John W. Vinson [MVP]

 

[efandango]

Douglas,

I have since realised that LatLongToXYZ is another code in another function
that was part of a module I took from Microsoft's Neatcode.Mdb file which
included the GreatArc function. I naively cut n pasted just that function
without realising that further down in the same module existed the function
for LatLongXYZ. So I deleted my original BasLong module and simpley imported
the whole Latitude/Longtitude function in my database and now the SQL is
working, except the results are not making sense, so I will look at the input
data, etc.

As you can tell I am a novice when it comes to programming and such, but
with the help and adviuce that people like yourself give, it becomes a great
deal easier to do something that doesn't come naturally to me.

What is LatLongToXYZ? Is it defined? If it is, what's the name of the module
in which it exists? If the module is also named LatLongToXYZ, rename the
module: modules cannot have the same names as routines.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Douglas,

I figured out the problem with the ambigous name, I googled around and
discovered other posts and solutions, whereby a guy had set up the
function
under another module; I took a look at modules and discovered I did
exaclty
the same thing, and deleted the offending module. However, now that my
module/function runs a bit further, I get this error message with the VBA
window:

Compile error:
Sub or function not defined; and it highlights this particular line:

LatLongToXYZ

within this larger line.
LatLongToXYZ Lat1, Lon1, Radius, X1, Y1, Z1

the full code:

*************************
Option Compare Database

Function GreatArcDistance(Lat1 As Double, Lon1 As Double, Lat2 As Double,
Lon2 As Double, Radius As Double) As Double
'
' Calculates the Great Arc (shortest) distance between 2 locations on the
globe.
'
' Uses functions from Trigonometry
'
Dim X1 As Double, Y1 As Double, Z1 As Double, X2 As Double, Y2 As Double,
Z2
As Double
Dim CosX As Double, ChordLen As Double
LatLongToXYZ Lat1, Lon1, Radius, X1, Y1, Z1
LatLongToXYZ Lat2, Lon2, Radius, X2, Y2, Z2
ChordLen = Sqr((X1 - X2) * (X1 - X2) + (Y1 - Y2) * (Y1 - Y2) + (Z1 - Z2)
*
(Z1 - Z2))
CosX = 1 - ChordLen * ChordLen / (2 * Radius * Radius)
Debug.Print X1, Y1, Z1
Debug.Print X2, Y2, Z2
Debug.Print ChordLen, CosX
If CosX = 1 Or CosX = -1 Then GreatArcDistance = 0
Else
GreatArcDistance = Sqr(1 - CosX * CosX) * Radius * PI() / 2
End If
End Function

*************************

Since Lat2 and Lon2 are computed values, you may have to repeat the
calculation:

SELECT [#Sample KML].name, [#Sample KML].address, [#Sample
KML].coordinates,
Mid([coordinates],InStr([coordinates],",")+1,Len([coordinates])-InStr([coordinates],",")-2)
AS Lat2, "-" &
Mid([coordinates],InStr([coordinates],"-")+1,Len([coordinates])-InStr([coordinates],",")-3)
AS Lon2, [#Sample KML].Lat1, [#Sample KML].Lon1, [#Sample KML].[Lat-Lon
Name],
GreatArcDistance([Lat1],[Lon1],Mid([coordinates],InStr([coordinates],",")+1,Len([coordinates])-InStr([coordinates],",")-2)
,Mid([coordinates],InStr([coordinates],"-")+1,Len([coordinates])-InStr([coordinates],",")-3)
,3963) AS Dist
FROM [#Sample KML];

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


The names (datatype) are as follows:

ID Autonumber
name Text
address Text
coordinates Text
Lat1 Text
Lon1 Text
Lat-Lon Name Text
Radius Text




:

What are the names of the fields in #SampleKML?

The error implies that Access thinks something in that function call
([Lat1], [Lon1], [Lat2] or [Lon2]) occurs more than once in
#SampleKML.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


John,

When I try to sun the query, I get the following error:
Ambigous name, in query expression
'GreatArcDistance([Lat1],[Lon1],[Lat2,[Lon2],3963'

This is the SQL

*****************
SELECT [#Sample KML].name, [#Sample KML].address, [#Sample
KML].coordinates,
Mid([coordinates],InStr([coordinates],",")+1,Len([coordinates])-InStr([coordinates],",")-2)
AS Lat2, "-" &
Mid([coordinates],InStr([coordinates],"-")+1,Len([coordinates])-InStr([coordinates],",")-3)
AS Lon2, [#Sample KML].Lat1, [#Sample KML].Lon1, [#Sample
KML].[Lat-Lon
Name], GreatArcDistance([Lat1],[Lon1],[Lat2],[Lon2],3963) AS Dist
FROM [#Sample KML];

*****************



This is the module code: (named as basLatLong)

*****************
Option Compare Database

Function GreatArcDistance(Lat1 As Double, Lon1 As Double, Lat2 As
Double,
Lon2 As Double, Radius As Double) As Double
'
' Calculates the Great Arc (shortest) distance between 2 locations
on
the
globe.
'
' Uses functions from Trigonometry
'
Dim X1 As Double, Y1 As Double, Z1 As Double, X2 As Double, Y2 As
Double,
Z2
As Double
Dim CosX As Double, ChordLen As Double
LatLongToXYZ Lat1, Lon1, Radius, X1, Y1, Z1
LatLongToXYZ Lat2, Lon2, Radius, X2, Y2, Z2
ChordLen = Sqr((X1 - X2) * (X1 - X2) + (Y1 - Y2) * (Y1 - Y2) +
(Z1 -
Z2)
*
(Z1 - Z2))
CosX = 1 - ChordLen * ChordLen / (2 * Radius * Radius)
Debug.Print X1, Y1, Z1
Debug.Print X2, Y2, Z2
Debug.Print ChordLen, CosX
If CosX = 1 Or CosX = -1 Then
GreatArcDistance = 0
Else
GreatArcDistance = Sqr(1 - CosX * CosX) * Radius * PI() / 2
End If
End Function
*****************

:

On Thu, 25 Oct 2007 14:51:01 -0700, efandango

I just don't quite know how to go about
building the process; in particular how to reference that large
block
of
'Great Arc ' code. which I seemingly cannot fit into a single
calculated
control. So what goes where?, so to speak. (this is really what I
meant
in my
previous post)

Pardon my misinterpretation!

Stepwise:

Select the Modules tab in your database window. Create a new
Module.

Copy and paste the VBA code into the module. Save it (click the
diskette
icon), but save it with a *DIFFERENT* name than the name of the
function -
basLatLong let's say.

On the menu select Debug... Compile <your database>. Fix any
compile
errors.

In the Query, simply type

Dist: GreatArcDistance([Lat1], [Lon1], [Lat2], [Lon2], 3963.)

is you want to use the equatorial radius of the Earth in miles, and
if
there
are fields in your query named Lat1, Lon1, Lat2 and Lon2 - replace
these
with
the actual fieldnames if not.


John W. Vinson [MVP]

 

[efandango]

Douglas, John, Piet...

Thank you all so much for your help on solving this problem for me. The
maths issue was resolved by retaining the - minus character on the Lon1
input, once I did this the decimals resolved from

19.4900553821136

to

9.98727752430737

problem now solved.

Phew!...

Thanks.

Douglas,

I have since realised that LatLongToXYZ is another code in another function
that was part of a module I took from Microsoft's Neatcode.Mdb file which
included the GreatArc function. I naively cut n pasted just that function
without realising that further down in the same module existed the function
for LatLongXYZ. So I deleted my original BasLong module and simpley imported
the whole Latitude/Longtitude function in my database and now the SQL is
working, except the results are not making sense, so I will look at the input
data, etc.

As you can tell I am a novice when it comes to programming and such, but
with the help and adviuce that people like yourself give, it becomes a great
deal easier to do something that doesn't come naturally to me.

What is LatLongToXYZ? Is it defined? If it is, what's the name of the module
in which it exists? If the module is also named LatLongToXYZ, rename the
module: modules cannot have the same names as routines.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Douglas,

I figured out the problem with the ambigous name, I googled around and
discovered other posts and solutions, whereby a guy had set up the
function
under another module; I took a look at modules and discovered I did
exaclty
the same thing, and deleted the offending module. However, now that my
module/function runs a bit further, I get this error message with the VBA
window:

Compile error:
Sub or function not defined; and it highlights this particular line:

LatLongToXYZ

within this larger line.
LatLongToXYZ Lat1, Lon1, Radius, X1, Y1, Z1

the full code:

*************************
Option Compare Database

Function GreatArcDistance(Lat1 As Double, Lon1 As Double, Lat2 As Double,
Lon2 As Double, Radius As Double) As Double
'
' Calculates the Great Arc (shortest) distance between 2 locations on the
globe.
'
' Uses functions from Trigonometry
'
Dim X1 As Double, Y1 As Double, Z1 As Double, X2 As Double, Y2 As Double,
Z2
As Double
Dim CosX As Double, ChordLen As Double
LatLongToXYZ Lat1, Lon1, Radius, X1, Y1, Z1
LatLongToXYZ Lat2, Lon2, Radius, X2, Y2, Z2
ChordLen = Sqr((X1 - X2) * (X1 - X2) + (Y1 - Y2) * (Y1 - Y2) + (Z1 - Z2)
*
(Z1 - Z2))
CosX = 1 - ChordLen * ChordLen / (2 * Radius * Radius)
Debug.Print X1, Y1, Z1
Debug.Print X2, Y2, Z2
Debug.Print ChordLen, CosX
If CosX = 1 Or CosX = -1 Then GreatArcDistance = 0
Else
GreatArcDistance = Sqr(1 - CosX * CosX) * Radius * PI() / 2
End If
End Function

*************************




:

Since Lat2 and Lon2 are computed values, you may have to repeat the
calculation:

SELECT [#Sample KML].name, [#Sample KML].address, [#Sample
KML].coordinates,
Mid([coordinates],InStr([coordinates],",")+1,Len([coordinates])-InStr([coordinates],",")-2)
AS Lat2, "-" &
Mid([coordinates],InStr([coordinates],"-")+1,Len([coordinates])-InStr([coordinates],",")-3)
AS Lon2, [#Sample KML].Lat1, [#Sample KML].Lon1, [#Sample KML].[Lat-Lon
Name],
GreatArcDistance([Lat1],[Lon1],Mid([coordinates],InStr([coordinates],",")+1,Len([coordinates])-InStr([coordinates],",")-2)
,Mid([coordinates],InStr([coordinates],"-")+1,Len([coordinates])-InStr([coordinates],",")-3)
,3963) AS Dist
FROM [#Sample KML];

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


The names (datatype) are as follows:

ID Autonumber
name Text
address Text
coordinates Text
Lat1 Text
Lon1 Text
Lat-Lon Name Text
Radius Text




:

What are the names of the fields in #SampleKML?

The error implies that Access thinks something in that function call
([Lat1], [Lon1], [Lat2] or [Lon2]) occurs more than once in
#SampleKML.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


John,

When I try to sun the query, I get the following error:
Ambigous name, in query expression
'GreatArcDistance([Lat1],[Lon1],[Lat2,[Lon2],3963'

This is the SQL

*****************
SELECT [#Sample KML].name, [#Sample KML].address, [#Sample
KML].coordinates,
Mid([coordinates],InStr([coordinates],",")+1,Len([coordinates])-InStr([coordinates],",")-2)
AS Lat2, "-" &
Mid([coordinates],InStr([coordinates],"-")+1,Len([coordinates])-InStr([coordinates],",")-3)
AS Lon2, [#Sample KML].Lat1, [#Sample KML].Lon1, [#Sample
KML].[Lat-Lon
Name], GreatArcDistance([Lat1],[Lon1],[Lat2],[Lon2],3963) AS Dist
FROM [#Sample KML];

*****************



This is the module code: (named as basLatLong)

*****************
Option Compare Database

Function GreatArcDistance(Lat1 As Double, Lon1 As Double, Lat2 As
Double,
Lon2 As Double, Radius As Double) As Double
'
' Calculates the Great Arc (shortest) distance between 2 locations
on
the
globe.
'
' Uses functions from Trigonometry
'
Dim X1 As Double, Y1 As Double, Z1 As Double, X2 As Double, Y2 As
Double,
Z2
As Double
Dim CosX As Double, ChordLen As Double
LatLongToXYZ Lat1, Lon1, Radius, X1, Y1, Z1
LatLongToXYZ Lat2, Lon2, Radius, X2, Y2, Z2
ChordLen = Sqr((X1 - X2) * (X1 - X2) + (Y1 - Y2) * (Y1 - Y2) +
(Z1 -
Z2)
*
(Z1 - Z2))
CosX = 1 - ChordLen * ChordLen / (2 * Radius * Radius)
Debug.Print X1, Y1, Z1
Debug.Print X2, Y2, Z2
Debug.Print ChordLen, CosX
If CosX = 1 Or CosX = -1 Then
GreatArcDistance = 0
Else
GreatArcDistance = Sqr(1 - CosX * CosX) * Radius * PI() / 2
End If
End Function
*****************

:

On Thu, 25 Oct 2007 14:51:01 -0700, efandango

I just don't quite know how to go about
building the process; in particular how to reference that large
block
of
'Great Arc ' code. which I seemingly cannot fit into a single
calculated
control. So what goes where?, so to speak. (this is really what I
meant
in my
previous post)

Pardon my misinterpretation!

Stepwise:

Select the Modules tab in your database window. Create a new
Module.

Copy and paste the VBA code into the module. Save it (click the
diskette
icon), but save it with a *DIFFERENT* name than the name of the
function -
basLatLong let's say.

On the menu select Debug... Compile <your database>. Fix any
compile
errors.

In the Query, simply type

Dist: GreatArcDistance([Lat1], [Lon1], [Lat2], [Lon2], 3963.)

is you want to use the equatorial radius of the Earth in miles, and
if
there
are fields in your query named Lat1, Lon1, Lat2 and Lon2 - replace
these
with
the actual fieldnames if not.


John W. Vinson [MVP]