I have two tables, Properties and Quotes. On the Quotes table, I have a portal that sorts the properties by distance. For example, I put in a Quote for Miami, Florida, and it pulls the Longitude and Latitude from a third table of Zip Codes, then sorts them in distance from Miami. This part works great, but now what I want to do is have a search that sorts the same properties by distance from an exact longitude and latitude, for example a starbucks in Miami. When I enter the If statement, it doesn't work correctly. It either shows a mix of the properties, or they all show a distance of zero. I'm not sure what i'm doing wrong...

Here is the calculation, which works fine (_lat & _lon are fields on the property records:

Let (

[

_R = 6367000

; _Lat1 = ( _Lat1 * Pi ) / 180

; _Lon1 = ( _Lon1 * Pi ) / 180

; _Lat2 = ( Quotes 2::_Lat2 * Pi ) / 180

; _Lon2 = ( Quotes 2::_Lon2 * Pi ) / 180

; _dlon = _Lon2 - _Lon1

; _dlat = _Lat2 - _Lat1

; _a = ( Sin ( _dlat / 2 ) ) ^ ( 2 ) + Cos ( _Lat1 ) * Cos ( _Lat2 ) * ( Sin ( _dlon / 2 ) ) ^ ( 2 )

; _Res = 2 * Asin ( Min ( 1 ; _a ^ ( 1 / 2 ) ) )

; _Final = _R * _Res

]

;

Round ( _Final / 1000 ; 2 )

)

-----------------------------------------

When I make it:

If ( Quotes 2::Target Longitude = "" ;

Let (

[

_R = 6367000

; _Lat1 = ( _Lat1 * Pi ) / 180

; _Lon1 = ( _Lon1 * Pi ) / 180

; _Lat2 = ( Quotes 2::_Lat2 * Pi ) / 180

; _Lon2 = ( Quotes 2::_Lon2 * Pi ) / 180

; _dlon = _Lon2 - _Lon1

; _dlat = _Lat2 - _Lat1

; _a = ( Sin ( _dlat / 2 ) ) ^ ( 2 ) + Cos ( _Lat1 ) * Cos ( _Lat2 ) * ( Sin ( _dlon / 2 ) ) ^ ( 2 )

; _Res = 2 * Asin ( Min ( 1 ; _a ^ ( 1 / 2 ) ) )

; _Final = _R * _Res

]

;

Round ( _Final / 1000 ; 2 )

)

;

Let (

[

_R = 6367000

; _Lat1 = ( _Lat1 * Pi ) / 180

; _Lon1 = ( _Lon1 * Pi ) / 180

; _Lat2 = ( Quotes 2::Target Latitude * Pi ) / 180

; _Lon2 = ( Quotes 2::Target Longitude * Pi ) / 180

; _dlon = _Lon2 - _Lon1

; _dlat = _Lat2 - _Lat1

; _a = ( Sin ( _dlat / 2 ) ) ^ ( 2 ) + Cos ( _Lat1 ) * Cos ( _Lat2 ) * ( Sin ( _dlon / 2 ) ) ^ ( 2 )

; _Res = 2 * Asin ( Min ( 1 ; _a ^ ( 1 / 2 ) ) )

; _Final = _R * _Res

]

;

Round ( _Final / 1000 ; 2 )

)

)

It doesn't work. Am I missing something?

Any advice would be great...

Thanks!

How about consolidating those calcs (not tested):

Let (

[

_R = 6367000

; _Lat1 = ( _Lat1 * Pi ) / 180

; _Lon1 = ( _Lon1 * Pi ) / 180

; _TARGLON = Quotes 2::Target Longitude

; _TARGLAT = Quotes 2::Target Latitude

; _QLON = Case( IsEmpty( TARGLON ) ; Quotes 2::_Lon2 ; TARGLON

; _QLAT = Case( IsEmpty( TARGLON ) ; Quotes 2::_Lat2 ; TARGLAT

; _Lat2 = ( _QLAT * Pi ) / 180

; _Lon2 = ( QLON * Pi ) / 180

; _dlon = _Lon2 - _Lon1

; _dlat = _Lat2 - _Lat1

; _a = ( Sin ( _dlat / 2 ) ) ^ ( 2 ) + Cos ( _Lat1 ) * Cos ( _Lat2 ) * ( Sin ( _dlon / 2 ) ) ^ ( 2 )

; _Res = 2 * Asin ( Min ( 1 ; _a ^ ( 1 / 2 ) ) )

; _Final = _R * _Res

; result = Round ( _Final / 1000 ; 2 )

]

;

result

)

I like to make the result a variable so that I can easily swap it out with other vars in the data viewer while testing.

this works perfectly for me and return is in miles

lat2 and lon2 is the starting coords, destination is lat1 and lon1

If(Lat1 ="" or Lon1="" or lat2="" or Lon2="";"";

If (Sin((Lat1/57.29577951))*Sin(lat2/57.29577951)+Cos(Lat1/57.29577951)*Cos(lat2/57.29577951)*Cos(Lon1/57.29577951-Lon2/57.29577951) > 1

; 3963.1*Acos(1);3963.1*Acos(Sin(Lat1/57.29577951)*Sin(lat2/57.29577951)+Cos(Lat1/57.29577951)*Cos(lat2/57.29577951)*Cos(Lon1/57.29577951-Lon2/57.29577951))))