convert MYSQL query into an Access 2007 SQL Server / ASP query

I have a MySql query working great but I can not convert it to Access 2007!

I'm getting an error that SQL in Access 2007 does not understand the acos, atn, and sin variables in a query that I have. This query works great in MySql but I'm having trouble writing it to work in Access 2007. The query is looking for all of the hospitals within a radius of 100 miles from a fixed lat/lon point in Berlin, Maryland (38.2287570, -75.21122790. The 3959 sets the Miles parameter and the radians equation is based on the ‘haversine’ formula to calculate the radius area.

Can anyone help me translate this MySql query into an Access 2007 query:

SELECT `ID`, `Hospital`, `City`, `State`, `Telephone`, ( 3959 * acos( cos( radians(38.2287570) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-75.2112790) ) + sin( radians(38.2287570) ) * sin( radians( lat ) ) ) ) AS distance FROM directors HAVING distance < 100 ORDER BY distance


THANKS!
LVL 4
kent3800Asked:
Who is Participating?
 
Patrick MatthewsCommented:
The following page gives a detailed description for how to implement the Haversine calculation in Access:

http://blogannath.blogspot.com/2010/04/microsoft-access-tips-tricks-great.html

It requires VBA, as not all of the necessary functions exist in native Access.
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Posted in the wrong section.  Access SQL is not the same as SQL Server TSQL.
0
 
Anthony PerkinsCommented:
You may have better luck if you post in the following more appropriate zone:
http://www.experts-exchange.com/Database/MS_Access/
0
 
kent3800Author Commented:
Hard stuff. Ended up just using the MySql web based method
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.