cfquery SQL problem 2

[sawedoff]

I have the following query in a zip code radius search, but it keeps bombing out on the ROUND and ORDER BY statements. What am I doing wrong?

<cfquery name=&quot;getlocs&quot; datasource=&quot;zips&quot;>
SELECT zipcode, latitude, longitude, state, city,
ROUND((ACos((SIN(#passedzip.latitude#/57.2958) * SIN(latitude/57.2958)) +
(COS(#passedzip.latitude#/57.2958) * COS(latitude/57.2958) *
COS(longitude/57.2958 - #passedzip.longitude#/57.2958))))
* 3963) AS [distance]
FROM ZIPcodes
WHERE latitude >= (#passedzip.latitude# - (#session.passedradius#/111)
And latitude <= #passedzip.latitude# + (#session.passedradius#/111)
AND longitude >= #passedzip.longitude# - (#session.passedradius#/111)
AND longitude <= #passedzip.longitude# + (#session.passedradius#/111)
ORDER BY distance
</cfquery>

Thanks.

 

[r937]

what database?

 

[sawedoff]

The table is zipcodes, in an Access database called zips.dbf.

 

[r937]

i ran your query against a dummy access table by substituting some arbitrary numbers for not only your coldfusion passedzip variables but also the columns that would've come out of the zipcodes table

got an error that said &quot;Undefined function ACos&quot;

took that part out and the rest ran okay (it just didn't return anything)

as far as your ORDER BY is concerned, you can substitute the column position number, i.e. ORDER BY 6, as it would be the 6th column

rudy

 

[sawedoff]

I had also gotten that message, but I can't get the thing to work without the ACos in the formula. The problem is, that ACos is a recognized function in ColdFusion, and necessary to the end result. Any ideas there?

 

[csteinhilber]

It doesn't matter if a function is recognized in ColdFusion, it's whether the function is supported by the database's (or, rather, the database driver's) SQL interpreter.

I would have to guess that ACOS isn't supported by the Access driver.

If I remember my trig... the formula for ArcCosine is

  Pi

/2 - ArcTan(

n

/sqrt(1-sqr(

n

)))

which may or may not help, if the Access driver doesn't support ArcTan either...

Have you tried using &quot;ARCCOS&quot; instead of &quot;ACOS&quot;. I can't seem to find a reference for the driver anywhere, but maybe they named the function slightly different.



-Carl

 

[csteinhilber]

By the way... how/why is your Access file named with a .DBF extension??



-Carl

 

[sawedoff]

Oops, it isn't a .dbf. I switch softwares all the time, and I just typed it wrong. It's really a .mdb. I haven't had a chance to try it again, but I should be able to get it to work with your answer. Thanks.

 

[kaigler]

sawedoff,

did you ever resolve the problem or did you move to a different database with the data? if you resolved this for access we would love to see what you did.

thanks