I'm trying to get the record in an Access database table holding known locations that is closest to some waypoint. Normally, to find the distance between two geographic points, you would use the Haversine formula, but that isn't possible in an Access query because some necessary math formulas are not available. That's no big deal, because I can readily narrow the search down to just a handful of points, but that is proving odd.

My idea was to get some fields, and the smallest distance using the Pythagorean Theorem:

Code:
SELECT TOP 1 HName,RouteGUID,Lat_WGS84,Lon_WGS84, MIN(SQR(
                                                                ((" & latitude.ToString & " - Lat_WGS84) * (" & latitude.ToString & " - Lat_WGS84))
                                                                + ((" & longitude.ToString & " - Lon_WGS84) * (" & longitude.ToString & " - Lon_WGS84))
                                                                )) AS Dist
		                                FROM GIS_Routing
                                        WHERE GIS_Routing.LLID = '" & LLID & "'
                                        GROUP BY HName, RouteGUID,Lat_WGS84,Lon_WGS84"
For the sake of discussion, the query could be simplified to:

Code:
SELECT TOP 1 HName,RouteGUID,Lat_WGS84,Lon_WGS84, MIN(Pythagoras) AS Dist
		                                FROM GIS_Routing
                                        WHERE GIS_Routing.LLID = '" & LLID & "'
                                        GROUP BY HName, RouteGUID,Lat_WGS84,Lon_WGS84"
That doesn't work, because Min appears to usually do nothing in this query. I get the records back in whatever order Access chooses.

I also thought to use Order By, which would be simpler...except that I can't order by Dist (the alias I gave the column). I believe I COULD use Order By, so long as I was willing to write out that entire equation in the Order By statement. That's not hard to do, since I can copy and paste, but it seems terribly inefficient, as it seems like it would require that the calculation be performed twice.


So, what is the best way to get the record with the smallest distance?