G
Guest
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.
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.