Results from local function won't compare

[joethall]

I have a table of lat/longs and I want to query for those that are more than 5 miles from a base lat/long. I wrote the function SMiles_Apart to do this calculation:

Public Function SMiles_Apart(Lat1 As Double, Lon1 As Double, Lat2 As Double, Lon2 As Double) As Double
'Compute distance between two points in Statute Miles (or km with alternate R value)
'  using the Haversine formula, which is more exact for smaller distances
'Lat and Lon must enter as decimal degrees
Dim difflon As Double, difflat As Double, A As Double, C As Double
  difflon = Lon2 - Lon1
  difflat = Lat2 - Lat1
  'A is half of the straight-line (chord) distance between the two points; 0.017453293 radians per degree
  A = (Sin(difflat * 0.017453293 / 2)) ^ 2 + Cos(Lat1 * 0.017453293) * Cos(Lat2 * 0.017453293) * (Sin(difflon * 0.017453293 / 2)) ^ 2
  'atan2(x,y) is arctan(x/y); but Excel reverses the normal order of the arguments
  '   and places the denominator as the first argument
  'For Excel VBA:  C = 2 * Application.WorksheetFunction.Atan2(Sqr(1 - A), Sqr(A))
  'For Access VBA: C = 2 * Atn(Sqr(A)/Sqr(1 - A))
  C = 2 * Atn(Sqr(A) / Sqr(1 - A))
  SMiles_Apart = 3956 * C 'R=3956 is radius of the earth in statute miles; or 40003.2 km
  'Result units are the same as the units of R in the previous line
End Function

If I run the query:

SELECT tblComps.Lat, tblComps.Long, (((SMiles_Apart([Lat],[Long],33.67864,-112.139)))) AS Miles
FROM tblComps
WHERE (((tblComps.Lat)>0) AND ((tblComps.Active)=True))
ORDER BY tblComps.ID;

I get reasonable with a Miles column that has reasonable numbers.



Now I just want those entries where Miles is greater than 100. So I add ">100" to the Miles Criteria field in Design Mode and I get "Data type mismatch in criteria expression".
The same happens with ">100.0" and "CDbl(100)" and">CDbl(100.0". I also notice that any of the .0 attempts are removed immediately.

Here's the SQL that should work:

SELECT tblComps.Lat, tblComps.Long, (((SMiles_Apart([Lat],[Long],33.67864,-112.139)))) AS Miles
FROM tblComps
WHERE (((tblComps.Lat)>0) AND (((((SMiles_Apart([Lat],[Long],33.67864,-112.139)))))>0) AND ((tblComps.Active)=True))
ORDER BY tblComps.ID;

Any thoughts would be MUCH appreciated!

Joe

[Dal Jeanis]

1) Have you tried Leaving the first query as is, and writing a second query against those results?

Select Lat, Long, Miles
FROM MyQuery1
WHERE Miles > 5;

2) Have you tried using WHERE Miles > 5.0000001?