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]