equation and error handling

[Aaron]

One table of stores.
Have a query that computes the distance based on lat/long.

A form let's the user input the center location (a city).
The query returns the distance of the stores to the input location.

Here is the equation in the distance field of the query:.

3963*(Atn(-(Sin([Forms]![INTER
FORM]![latb])*Sin([AvgOfLAT])+Cos([Forms]![INTER
FORM]![latb])*Cos([AvgOfLAT])*Cos([Forms]![INTER
FORM]![longa]-[AvgOfLONG]))/Sqr(-(Sin([Forms]![INTER
FORM]![latb])*Sin([AvgOfLAT])+Cos([Forms]![INTER
FORM]![latb])*Cos([AvgOfLAT])*Cos([Forms]![INTER
FORM]![longa]-[AvgOfLONG]))*(Sin([Forms]![INTER
FORM]![latb])*Sin([AvgOfLAT])+Cos([Forms]![INTER
FORM]![latb])*Cos([AvgOfLAT])*Cos([Forms]![INTER
FORM]![longa]-[AvgOfLONG]))+1))+(2*Atn(1)))

With no direct arcos function in access, it adds to the length.

My issue is this:

If a user enters in the same city as one of the stores, then an #error will
show up as the distance. The distance is calculated fine for other cities.
I tried applying an iserror() function, and it returned false for all of the
other cities (as they are not errors), but instead of returning true (or 1),
it still returned #error.

I can't figure this one out. At the very least, I want it to return zero if
there is an error for the distance calculation, but why can't the distance
calculation calculate if the two coordinates are equal? does this have to do
with sin/cos properties?
it works fine in excel to calculate equal coordinates.

Thanks for the help.

[Douglas J. Steele]

Assuming that you're running from within Access, you could write a function
that does the calculation, and use the function in your query, rather than
having that long equation in-line.

Your function could determine whether or not the input is the same city as
an existing store.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

"Aaron" <Aa...@discussions.microsoft.com> wrote in message
news:CAEDF988-D809-4B7A...@microsoft.com...

[James A. Fortune]

Make the derived function (from the help file) a public function in a
module:

Public Function ArcCos(X As Double) As Double
ArcCos = Atn(-X / Sqr(-X * X + 1)) + 2 * Atn(1)
End Function

It looks like your value for X looks something like:

X = Sin(latb) * Sin(AvgOfLat) + Cos(latb) * Cos(longa) - AvgOfLONG

So the distance becomes 3963 * ArcCos(X)

The input to ArcCos must be between -1 and 1. If the value of X you get
is not between those values then ArcCos is undefined since the Cos
function defined on Real domains only returns values between -1 and 1.
AFAICS, every value between -1 and 1 should return the correct value
using the derived function above. In other words, check to see if your
formula is giving you values between -1 and 1. If not, I suspect that
the formula you are using is not of sufficient generality for your purposes.

For a Great Circle Arc Length:

distance = Re * Theta12

where

Re = Radius of the Earth
Theta12 = ArcCos(UnitVectorX1 Dot UnitVectorX2)
UnitVectorX1 points to location1
UnitVectorX2 points to location2
ArcCos is a radian measure

Your formula looks a little suspect. My intuition tells me that it
should look something like:

Spherical to Rectangular conversion formulae:
x = r * Cos(latx) * Cos(longx)
y = r * Cos(latx) * Sin(longx)
z = r * Sin(latx)

x^2 + y^2 + z^2 = r^2 * Cos(latx) ^ 2 (Cos(longx) ^ 2 + Sin(longx) ^2) +
r^2 * Sin(latx) ^ 2
= r^2 * Cos(latx) ^ 2 + r^2 * Sin(latx) ^ 2 = r ^ 2

So [x/r, y/r, z/r] is a unit vector.

unit vector = [Cos(latx) * Cos(longx), Cos(latx) * Sin(longx), Sin(latx)]

X = ua dot ub = Cos(lata) * Cos(longa) * Cos(latb) * Cos(longb) +
Cos(lata) * Sin(longa) * Cos(latb) * Sin(longb) + Sin(lata) * Sin(latb)

distance = R3 * ArcCos(X)

X in that form should not cause any trouble. Note that I don't have a
math book here and I'm a little rusty with spherical coordinates.
Perhaps your refined formula is equivalent. I didn't test any of this.
This was a lot of fun for me.

I hope this helps,

James A. Fortune
MPAP...@FortuneJames.com

[James A. Fortune]

One other thing. The longitude is positive in the opposite direction
from normal spherical coordinates so perhaps the following identities
will help:

Cos(-X) = Cos(X)
Sin(-X) = - Sin(X)

James A. Fortune
MPAP...@FortuneJames.com