Can I include VB Function in Query?

Question

User-770881519 posted

I have an access MDB database of latitude and longitude coordinates (x,y). I also have a function in VB similar to:

Public Function Distance (x1,y1,x2,y2) as Double
'Some code here
Distance = 'distance between x1,y1 and x2,y2
End Function

What I want to do is create a query that will show all locations contained in the table that are within 10 miles of user unetered coordinates (where LAT and LON are columns in the table and VarX and VarY are user proivided:
SELECT *, Distance([LAT],[LON],VarX,VarY) as Dist FROM TableName WHERE Dist < 10

Obviously this doesn't work but I want to know how to accomplish this end result. Basically, I want to return a query of all locations within 10 miles of VarX and VarY. How do I incororate the Distance function into my query?

If it's not possible, what is the best workaround to accomplish this?

Using ASP.NET Visual Basic and a MS Access MDB 

Answer 1

User-1666980993 posted

The best solution in this case is to use a distance calculation.  Here are some links to latitude/longitude distance calculations:

http://jan.ucc.nau.edu/~cvm/latlon_formula.html

http://mathforum.org/library/drmath/view/51879.html

If you were using a SQL Server or other RDBMS, you could build a view or user-defined function that could return the distance from each location to the entered long/lat.  Microsoft Access isn't as powerful, so you'll need to find an efficient way to test the entered long/lat against every location entry in your database. 

Answer 2

User-770881519 posted

Thanks for the input.  The actual formula for the distance I do have.  I am just stuck as to where to go from here.  If I were to convert my access DB to MSSQL, do you know where some good sources of info would be on how to craft a user defined function in SQL?