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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kent3800Author Commented:
Hard stuff. Ended up just using the MySql web based method
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.