acos function - distance between zip codes

[Guest]

I am trying to find the distance between two zip codes and i found this
equation on line:

select
acos(
( sin(z1.latitude * 0.017453293)*sin(z2.latitude * 0.017453293) )
+
( cos(z1.latitude * 0.017453293) *
cos(z2.latitude * 0.017453293) *
cos((z2.longitude*0.017453293)-(z1.longitude*0.017453293))
)
)
* 3956 as distance
from [zip code info] z1, [zip code info] z2
where z1.zipcode = 'mi49512' and z2.zipcode = 'mi49401';


however access keeps telling me that the acos function is not defined. I
looked it up at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_5a5v.asp

so i was wondering if i am doing something wrong or if access does not
support it.

Is there anyother way to calculate the distance between zip codes in access

 

[Douglas J. Steele]

The URL you've cited is to Transact SQL, which is for SQL Server, not
Access.

According to the Access Help file, Arccos(X) = Atn(-X / Sqr(-X * X + 1)) + 2
* Atn(1)

 

[Guest]

Ok thanks so much that worked perfectly. Now I have a query that that
calculates the distance between two zip codes. When you open the query it
askes you for two variables distance1 and distance2 and then calculates the
distances between them. Now I have a form and I was wondering if i can set a
text box to equal the result from this querry. I tried to set the text box to
equal =query![distance finder].distance and i changed distance1 and distance2
to equal the text boxes on a form. If i have the form open and i open the
query it calculates the distance from the two text boxes on the form, so i
was wondering how would i set a text box on my form to equal this calculated
distance value.

 

[Arvin Meyer]

With a form, you have multiple choices:

1. You can bind it to the query and set the textbox = to the result.
2. You can also feed query criteria from textboxes on the form and requery
them.
3. You can simply run the calculation from code directly on the form.

1: Is self explanatory.

2: Set the criteria boxes in the query to:

Forms!YourFormName!YourTextboxName

requery the results in the AfterUpdate event of each textbox.

3: A bit of code like (aircode) in each textbox's AfterUpdate event:

Private Sub Text1_AfterUpdate()
If Not IsNull(Me.Text2) Then
Me.Text3 = ' Your expression
End If
End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Douglas J. Steele]

If you've got a query that returns the distance as a value, simply set the
form's Record Source to that query, and set the text box's Control Source to
the name of the field in the query.

 

[adsl]

I am trying to find the distance between two zip codes and i found this
equation on line:

select
acos(
( sin(z1.latitude * 0.017453293)*sin(z2.latitude * 0.017453293) )
+
( cos(z1.latitude * 0.017453293) *
cos(z2.latitude * 0.017453293) *
cos((z2.longitude*0.017453293)-(z1.longitude*0.017453293))
)
)
* 3956 as distance
from [zip code info] z1, [zip code info] z2
where z1.zipcode = 'mi49512' and z2.zipcode = 'mi49401';


however access keeps telling me that the acos function is not defined. I
looked it up at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_5a5v.asp

so i was wondering if i am doing something wrong or if access does not
support it.

Is there anyother way to calculate the distance between zip codes in
access

 

[adsl]

The URL you've cited is to Transact SQL, which is for SQL Server, not
Access.

According to the Access Help file, Arccos(X) = Atn(-X / Sqr(-X * X + 1)) +
2 * Atn(1)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am trying to find the distance between two zip codes and i found this
equation on line:

select
acos(
( sin(z1.latitude * 0.017453293)*sin(z2.latitude * 0.017453293) )
+
( cos(z1.latitude * 0.017453293) *
cos(z2.latitude * 0.017453293) *
cos((z2.longitude*0.017453293)-(z1.longitude*0.017453293))
)
)
* 3956 as distance
from [zip code info] z1, [zip code info] z2
where z1.zipcode = 'mi49512' and z2.zipcode = 'mi49401';


however access keeps telling me that the acos function is not defined. I
looked it up at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_5a5v.asp

so i was wondering if i am doing something wrong or if access does not
support it.

Is there anyother way to calculate the distance between zip codes in
access

 

[adsl]

Ok thanks so much that worked perfectly. Now I have a query that that
calculates the distance between two zip codes. When you open the query it
askes you for two variables distance1 and distance2 and then calculates
the
distances between them. Now I have a form and I was wondering if i can set
a
text box to equal the result from this querry. I tried to set the text box
to
equal =query![distance finder].distance and i changed distance1 and
distance2
to equal the text boxes on a form. If i have the form open and i open the
query it calculates the distance from the two text boxes on the form, so i
was wondering how would i set a text box on my form to equal this
calculated
distance value.

 

[adsl]

With a form, you have multiple choices:

1. You can bind it to the query and set the textbox = to the result.
2. You can also feed query criteria from textboxes on the form and requery
them.
3. You can simply run the calculation from code directly on the form.

1: Is self explanatory.

2: Set the criteria boxes in the query to:

Forms!YourFormName!YourTextboxName

requery the results in the AfterUpdate event of each textbox.

3: A bit of code like (aircode) in each textbox's AfterUpdate event:

Private Sub Text1_AfterUpdate()
If Not IsNull(Me.Text2) Then
Me.Text3 = ' Your expression
End If
End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

Ok thanks so much that worked perfectly. Now I have a query that that
calculates the distance between two zip codes. When you open the query it
askes you for two variables distance1 and distance2 and then calculates the
distances between them. Now I have a form and I was wondering if i can
set a
text box to equal the result from this querry. I tried to set the text
box to
equal =query![distance finder].distance and i changed distance1 and distance2
to equal the text boxes on a form. If i have the form open and i open the
query it calculates the distance from the two text boxes on the form, so
i
was wondering how would i set a text box on my form to equal this calculated
distance value.

 

[adsl]

If you've got a query that returns the distance as a value, simply set the
form's Record Source to that query, and set the text box's Control Source
to the name of the field in the query.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Ok thanks so much that worked perfectly. Now I have a query that that
calculates the distance between two zip codes. When you open the query it
askes you for two variables distance1 and distance2 and then calculates
the
distances between them. Now I have a form and I was wondering if i can
set a
text box to equal the result from this querry. I tried to set the text
box to
equal =query![distance finder].distance and i changed distance1 and
distance2
to equal the text boxes on a form. If i have the form open and i open the
query it calculates the distance from the two text boxes on the form, so
i
was wondering how would i set a text box on my form to equal this
calculated
distance value.

 

[Tony Toews]

I am trying to find the distance between two zip codes

Note though that some zip codes can cover quite a large area. Thus
your results might have a bit of an error.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm