Square Root Calculation in Access

Posted on 2004-11-13
Last Modified: 2012-05-05

I am trying to calculate the distance between two points/buildings using Grid Reference's, heres what I have in Excel

Building 1 (B1)
B1 GRID North =  100500
B1 GRID East  =   100500

Building 2 (B2)
B2 GRID North =  100600
B2 GRID East  =   100700

=SQRT(((B2 GRID North-B1 GRID North)^2)+((B2 GRID East-B1 GRID East)^2))

Result: 223.6

Now I have tried using the same formula in an Access Select Query but can not use the same function SQRT, it does not exist? When I run the Quesry I get the message "Undefined function "SQRT" in expression".
If I use the SQR function this give me a higher value, I am not sure what to do?????

Please HELP!
Question by:Akkas
    LVL 33

    Assisted Solution

    by:Mike Eghtebas

    =(((B2 GRID North-B1 GRID North)^2)+((B2 GRID East-B1 GRID East)^2))^0.5
    LVL 48

    Expert Comment

    by:Gustav Brock
    In Access it is called SQR():

    =SQR(([B2 GRID North]-[B1 GRID North])^2+([B2 GRID East]-[B1 GRID East])^2)


    Author Comment

    The formula works great in Excel, as an alternative. However this does not operate in Microsoft Access. Results are far from what is achieved in Excel.

    Cactus Data:
    The result of the expression in Access is far less then my original result in Excel. I was expecting a value of 223.6 & I got 141.421.

    I have played with these statements but have not had much luck in modifying them to provide a spot on result.

    More ideas and suggestions please!!!
    LVL 48

    Accepted Solution

    From your own parameters:

    ? SQR((100600-100500)^2+(100700-100500)^2)


    Author Comment

    I was working late last night until 5am U.K. time, I picked up your statement and entered it incorrectly and kept on entering it in incorrectly..
    I got it now..Thanks
    LVL 48

    Expert Comment

    by:Gustav Brock
    Oh Boy. You better get a good night's sleep!


    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Suggested Solutions

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now