Module Challenged Using Lat/Long Coordinates

[Guest]

Using WinXP, Access2K2

In Microsoft Excel, slightly modified for Access, I found this formula to
calculate the
distance between two points on the earth, using latitude and longitude in
decimal degrees:

=((3443.917)*ACOS(Cos(90-([strLat])))*Cos(90-([MyLat]))+Sin(90-([strLat]))*Sin(90-([MyLat]))*Cos([strLong]-[MyLong]))*1.15078
(The last number converts nautical miles to standard miles.)

Access does not have an ArcCosine function, but found the following code on
another Access information site:

Function ACos(X As Double) As Double
' Arccos the same as ACOS in Excel
ACos = Atn(-X / Sqr(-X * X + 1)) + 2 * Atn(1)
End Function

On another information site, a person recommended putting the previous code
in a Module.

This is where I'm totally lost. I understand there is a Standard module and
a Form module, but I don't know how to determine what to do with that.

I also don't understand how to get X into the Function. (I believe it is an
integer between 1 and -1). And how do I use the module in my formula?

Thanks in advance.

 

[John W. Vinson]

Using WinXP, Access2K2

In Microsoft Excel, slightly modified for Access, I found this formula to
calculate the
distance between two points on the earth, using latitude and longitude in
decimal degrees:

=((3443.917)*ACOS(Cos(90-([strLat])))*Cos(90-([MyLat]))+Sin(90-([strLat]))*Sin(90-([MyLat]))*Cos([strLong]-[MyLong]))*1.15078
(The last number converts nautical miles to standard miles.)

Access does not have an ArcCosine function, but found the following code on
another Access information site:

Function ACos(X As Double) As Double
' Arccos the same as ACOS in Excel
ACos = Atn(-X / Sqr(-X * X + 1)) + 2 * Atn(1)
End Function

On another information site, a person recommended putting the previous code
in a Module.

This is where I'm totally lost. I understand there is a Standard module and
a Form module, but I don't know how to determine what to do with that.

Just open the Modules tab in the database window; create a new Module. Copy
and paste the four lines (well, three and a comment) into it. Select Debug...
Compile <my database> to compile the routine and check for errors. Save it
with some name OTHER than ACos - the name of the module must be different than
the name of any of the functions or subs in it; maybe save it as
basTrigonometry.

I also don't understand how to get X into the Function. (I believe it is an
integer between 1 and -1). And how do I use the module in my formula?

Once you've defined the function ACos, it is available just as if it were a
builtin function. Your lat-long expression above will work as written, and you
shouldn't need to do anything other than use it. Calling the function with

ACOS(Cos(90-([strLat])))

will evaluate the value of the cosine function, which will be a number between
-1 and 1, and pass that value to your custom ACos function; within the
function, whatever was passed will be put into the variable X and used within
the function.

John W. Vinson [MVP]

 

[Guest]

John,

Thanks for the module explanation. I had used the same module name as the
function name. I changed the module name and the errors disappeared.

There must be something else I'm missing because the results are very
different in Access compared to Excel, using the same coordinate values.

The following is the formula in Excel that I got from:
http://www.cpearson.com/excel/latlong.htm
I checked it against Google Earth and it works for me.

=RadiusEarth*ACOS(COS(RADIANS(90-(Lat1d)))*COS(RADIANS(90-(Lat2d)))+SIN(RADIANS(90-(Lat1d)))*SIN(RADIANS(90-(Lat2d)))*COS(RADIANS((Long1d-Long2d))))

Access does not need the "RADIANS" part of the formula because Access works
in radians already. (At least that is what I understand.)

After removing the "RADIANS" from the Excel formula, I came up with this:

=3443.917*ACos(Cos(90-[strLat])*Cos(90-[MyLat])+Sin(90-[strLat])*Sin(90-[MyLat])*Cos([strLong]-[MyLong]))

Substituting:
RadiusEarth = 3443.917 (Nautical Miles)
Lat1d = [strLat]; Lat2d = [MyLat]
Long1d = [strLong]; Long2d = [MyLong]

[strLat] and [strLong] are both bound text boxes to an underlying table.
[MyLat] and [MyLong] are unbound text boxes on a form, which have fixed
values.

If my converted formula is correct, then could the ACos function I'm using
be in error?

--
Jim Ory

Using WinXP, Access2K2

In Microsoft Excel, slightly modified for Access, I found this formula to
calculate the
distance between two points on the earth, using latitude and longitude in
decimal degrees:

=((3443.917)*ACOS(Cos(90-([strLat])))*Cos(90-([MyLat]))+Sin(90-([strLat]))*Sin(90-([MyLat]))*Cos([strLong]-[MyLong]))*1.15078
(The last number converts nautical miles to standard miles.)

Access does not have an ArcCosine function, but found the following code on
another Access information site:

Function ACos(X As Double) As Double
' Arccos the same as ACOS in Excel
ACos = Atn(-X / Sqr(-X * X + 1)) + 2 * Atn(1)
End Function

On another information site, a person recommended putting the previous code
in a Module.

This is where I'm totally lost. I understand there is a Standard module and
a Form module, but I don't know how to determine what to do with that.

Just open the Modules tab in the database window; create a new Module. Copy
and paste the four lines (well, three and a comment) into it. Select Debug...
Compile <my database> to compile the routine and check for errors. Save it
with some name OTHER than ACos - the name of the module must be different than
the name of any of the functions or subs in it; maybe save it as
basTrigonometry.

I also don't understand how to get X into the Function. (I believe it is an
integer between 1 and -1). And how do I use the module in my formula?

Once you've defined the function ACos, it is available just as if it were a
builtin function. Your lat-long expression above will work as written, and you
shouldn't need to do anything other than use it. Calling the function with

ACOS(Cos(90-([strLat])))

will evaluate the value of the cosine function, which will be a number between
-1 and 1, and pass that value to your custom ACos function; within the
function, whatever was passed will be put into the variable X and used within
the function.

John W. Vinson [MVP]

 

[i_takeuti]

Using WinXP, Access2K2

In Microsoft Excel, slightly modified for Access, I found this formula to
calculate the
distance between two points on the earth, using latitude and longitude in
decimal degrees:

=((3443.917)*ACOS(Cos(90-([strLat])))*Cos(90-([MyLat]))+Sin(90-([strLat]))*Sin(90-([MyLat]))*Cos([strLong]-[MyLong]))*1.15078
(The last number converts nautical miles to standard miles.)

Access does not have an ArcCosine function, but found the following code
on
another Access information site:

Function ACos(X As Double) As Double
' Arccos the same as ACOS in Excel
ACos = Atn(-X / Sqr(-X * X + 1)) + 2 * Atn(1)
End Function

On another information site, a person recommended putting the previous
code
in a Module.

This is where I'm totally lost. I understand there is a Standard module
and
a Form module, but I don't know how to determine what to do with that.

Just open the Modules tab in the database window; create a new Module.
Copy
and paste the four lines (well, three and a comment) into it. Select
Debug...
Compile <my database> to compile the routine and check for errors. Save it
with some name OTHER than ACos - the name of the module must be different
than
the name of any of the functions or subs in it; maybe save it as
basTrigonometry.

I also don't understand how to get X into the Function. (I believe it is
an
integer between 1 and -1). And how do I use the module in my formula?

Once you've defined the function ACos, it is available just as if it were
a
builtin function. Your lat-long expression above will work as written, and
you
shouldn't need to do anything other than use it. Calling the function with

ACOS(Cos(90-([strLat])))

will evaluate the value of the cosine function, which will be a number
between
-1 and 1, and pass that value to your custom ACos function; within the
function, whatever was passed will be put into the variable X and used
within
the function.

John W. Vinson [MVP]

 

[John W. Vinson]

Access does not need the "RADIANS" part of the formula because Access works
in radians already. (At least that is what I understand.)

After removing the "RADIANS" from the Excel formula, I came up with this:

=3443.917*ACos(Cos(90-[strLat])*Cos(90-[MyLat])+Sin(90-[strLat])*Sin(90-[MyLat])*Cos([strLong]-[MyLong]))

That's using 90 radians as a right angle. Replace the 90 with 0.785398163
(pi/4) and make sure that all your latitudes and longitudes are also in
radians (I doubt it very much!!!) and you should get the correct results.

Access *WORKS* in radians - but that doesn't mean that your users store their
latitudes and longitudes in radians!

John W. Vinson [MVP]

 

[James A. Fortune]

John,

Thanks for the module explanation. I had used the same module name as the
function name. I changed the module name and the errors disappeared.

There must be something else I'm missing because the results are very
different in Access compared to Excel, using the same coordinate values.

The following is the formula in Excel that I got from:
http://www.cpearson.com/excel/latlong.htm
I checked it against Google Earth and it works for me.

=RadiusEarth*ACOS(COS(RADIANS(90-(Lat1d)))*COS(RADIANS(90-(Lat2d)))+SIN(RADIANS(90-(Lat1d)))*SIN(RADIANS(90-(Lat2d)))*COS(RADIANS((Long1d-Long2d))))

Access does not need the "RADIANS" part of the formula because Access works
in radians already. (At least that is what I understand.)

After removing the "RADIANS" from the Excel formula, I came up with this:

That was a bad move.


From the A97 Help File (Cos Function):

"...

Cos(number)

The required number argument is a Double or any valid numeric expression
that expresses an angle in radians.

....

To convert degrees to radians, multiply degrees by pi/180. To convert
radians to degrees, multiply radians by 180/pi."


Access works in radians already so you must supply radians to the
function, not degrees. You need to multiply your degrees by pi/180 to
convert them to radians. I use:

Global Const Deg2Rad = 1.74532925199433E-02

=3443.917*ACos(Cos(90-[strLat])*Cos(90-[MyLat])+Sin(90-[strLat])*Sin(90-[MyLat])*Cos([strLong]-[MyLong]))

Substituting:
RadiusEarth = 3443.917 (Nautical Miles)
Lat1d = [strLat]; Lat2d = [MyLat]
Long1d = [strLong]; Long2d = [MyLong]

[strLat] and [strLong] are both bound text boxes to an underlying table.
[MyLat] and [MyLong] are unbound text boxes on a form, which have fixed
values.

If my converted formula is correct, then could the ACos function I'm using
be in error?

Otherwise, the formula is correct because it is essentially the same as
the one I posted here:

http://groups.google.com/group/microsoft.public.access/browse_frm/thread/b7baa12e5809aa90

which should work correctly as is for East Longitudes and North
Latitudes given in radians (BTW, why nautical miles?). To show that the
other formula is equivalent to mine, apply the following trig identities
to the other formula:

Cos(-x) = Cos(x)
Sin(-x) = - Sin(x)
Cos(x - y) = Cos(x) * Cos(y) + Sin(x) * Sin(y)
Sin(x - y) = Sin(x) * Cos(y) - Sin(y) * Cos(x)

Note that Cos(90 deg) = 1 and Sin(90 deg) = 0.

Your ACos function looks fine since it is just like the one from the
help file.

I hope this helps.

James A. Fortune
(e-mail address removed)

 

[MPAPoster]

Note that Cos(90deg) = 1 and Sin(90deg) = 0.

Wow. I must have had my head twisted 90 degrees to the left when I
said that.

Cos(90 deg) = 0
Sin(90 deg) = 1

James A. Fortune
(e-mail address removed)

 

[John W. Vinson]

Wow. I must have had my head twisted 90 degrees to the left when I
said that.

Answering machine message:

"The number you have reached is an imaginary number. Please hang up, rotate
your phone 90 degrees, and dial again."

John W. Vinson [MVP]

 

[Guest]

Many thanks to John and James for getting my head thinking about Trigonometry
again. The conversion to Radians was my downfall. I could not even get close
to coming up with the correct answer using the Excel formula in Access. Even
the conversion from degrees to radians is different than what was explained
to me.

However, after a few more Internet searches, from this site:
http://www.meridianworlddata.com/Distance-Calculation.asp I found this
formula and modified it to fit my controls.

Radius of Earth in Statute miles = 3963.0

=3963.0 * acos(sin(strLat/57.2958) * sin(MyLat/57.2958) +
cos(strLat/57.2958) * cos(MyLat/57.2958) * cos(MyLong/57.2958
-strLong/57.2958))

This formula works great in Access and is accurate enough for my usage.
1/100 of a mile is the difference between the Excel formula and the Access
formula above. Close enough.

In case you're wondering what I am using this for; I am an Amateur Radio
operator and I contact Amateurs all over the world on the radio. Latitude and
Longitude are common items to post where one lives. I designed a program in
Access to log my contacts/conversations over the air and now I have the
formula to automatically determine the distance to the other Amateur radio
station.

Thanks for taking the time to help me with this.

--
Jim Ory

John,

Thanks for the module explanation. I had used the same module name as the
function name. I changed the module name and the errors disappeared.

There must be something else I'm missing because the results are very
different in Access compared to Excel, using the same coordinate values.

The following is the formula in Excel that I got from:
http://www.cpearson.com/excel/latlong.htm
I checked it against Google Earth and it works for me.

=RadiusEarth*ACOS(COS(RADIANS(90-(Lat1d)))*COS(RADIANS(90-(Lat2d)))+SIN(RADIANS(90-(Lat1d)))*SIN(RADIANS(90-(Lat2d)))*COS(RADIANS((Long1d-Long2d))))

Access does not need the "RADIANS" part of the formula because Access works
in radians already. (At least that is what I understand.)

After removing the "RADIANS" from the Excel formula, I came up with this:

That was a bad move.


From the A97 Help File (Cos Function):

"...

Cos(number)

The required number argument is a Double or any valid numeric expression
that expresses an angle in radians.

....

To convert degrees to radians, multiply degrees by pi/180. To convert
radians to degrees, multiply radians by 180/pi."


Access works in radians already so you must supply radians to the
function, not degrees. You need to multiply your degrees by pi/180 to
convert them to radians. I use:

Global Const Deg2Rad = 1.74532925199433E-02

=3443.917*ACos(Cos(90-[strLat])*Cos(90-[MyLat])+Sin(90-[strLat])*Sin(90-[MyLat])*Cos([strLong]-[MyLong]))

Substituting:
RadiusEarth = 3443.917 (Nautical Miles)
Lat1d = [strLat]; Lat2d = [MyLat]
Long1d = [strLong]; Long2d = [MyLong]

[strLat] and [strLong] are both bound text boxes to an underlying table.
[MyLat] and [MyLong] are unbound text boxes on a form, which have fixed
values.

If my converted formula is correct, then could the ACos function I'm using
be in error?

Otherwise, the formula is correct because it is essentially the same as
the one I posted here:

http://groups.google.com/group/microsoft.public.access/browse_frm/thread/b7baa12e5809aa90

which should work correctly as is for East Longitudes and North
Latitudes given in radians (BTW, why nautical miles?). To show that the
other formula is equivalent to mine, apply the following trig identities
to the other formula:

Cos(-x) = Cos(x)
Sin(-x) = - Sin(x)
Cos(x - y) = Cos(x) * Cos(y) + Sin(x) * Sin(y)
Sin(x - y) = Sin(x) * Cos(y) - Sin(y) * Cos(x)

Note that Cos(90 deg) = 1 and Sin(90 deg) = 0.

Your ACos function looks fine since it is just like the one from the
help file.

I hope this helps.

James A. Fortune
(e-mail address removed)

 

[John W. Vinson]

In case you're wondering what I am using this for; I am an Amateur Radio
operator and I contact Amateurs all over the world on the radio. Latitude and
Longitude are common items to post where one lives. I designed a program in
Access to log my contacts/conversations over the air and now I have the
formula to automatically determine the distance to the other Amateur radio
station.

Cool!

Access MVP Tony Toews (www.granite.ab.ca/access) might be interested, I'll
refer him to this thread. He's an active ham radio operator.

John W. Vinson [MVP]