Solved

# Square Root Calculation in Access

Posted on 2004-11-13
414 Views
Wizards,

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

Akkas
0
Question by:Akkas

LVL 33

Assisted Solution

Use:

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

LVL 48

Expert Comment

In Access it is called SQR():

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

/gustav
0

Author Comment

eghtebas:
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.

0

LVL 48

Accepted Solution

? SQR((100600-100500)^2+(100700-100500)^2)
223,606797749979

/gustav
0

Author Comment

Gustav:
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
Akkas
0

LVL 48

Expert Comment

Oh Boy. You better get a good night's sleep!

/gustav
0

## Featured Post

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