Problem with an If statement in a calculation...

[JTSmith]

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!

[Fitch]

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.

[No_access]

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))))