POW() SQL Function, CF4, and Access 2000

My delima is simple enough... or so I thought!

Here's the code:
<!--- Origin Zip Code --->
<cfset origZip = 33990>

<!--- Distance in Miles --->
<cfset distLimit = 10>

<!--- This query works --->
<cfquery name="zipfrom" datasource="********">
SELECT latitude, longitude
FROM zipdata
WHERE zip = '#origZip#'
</cfquery>

<!--- This one fails --->
<cfquery name="ziplist" datsource="********">
SELECT zipcode
FROM zipdata
WHERE (pow((69.1 * (zipdata.longitude - #zipfrom.longitude#) * cos(#zipfrom.latitude# / 57.3)), 2) + pow((69.1 * (zipdata.latitude - #zipfrom.latitude#)), 2)) < (#distLimit# * #distLimit#)
</cfquery>

<p><cfoutput query="ziplist">#zip#, </cfoutput></p>

Problem:[Microsoft][ODBC Microsoft Access Driver] Undefined function 'pow' in expression.



Does anybody know if there is an equivalent to the pow() SQL function when querying MS Access 2000 databases? I have Googled the #&%! out of this topic and could find no useable reference.

BTW: This problem is holding up a project I am charging for. Therefore, if there is a reasonable price to be paid for the CORRECT response, I would gladly entertain the possibility.

i'm guessing the pow() function is just exponentiation?

it's defined in mysql, is that where you got it?

in access you'd use the ^ operator

p.s.

instead of

<p><cfoutput query="ziplist">#zip#, </cfoutput></p>

you can do this:

<p><cfoutput>#ValueList(ziplist.zipcode)#</cfoutput></p>

and you won't have a trailing comma

Thank you very much for the quick reply.

My query executes somewhat slow but that may be do to users currently accessing the database.

As for the reference to the pow() function, the first place I found it was in a php example (MySQL) and then found reference to the same function in the SQL 97 specifications. Therefore I assumed the function had to exist in Access.

which it does (thanks for pointing out the syntax), it's just not a "named" function as I would have expected

Can somebody fix this code for Access for me? I have tried for days and can't figure it out. Thankyou.

SELECT *
FROM tblCustomer2
WHERE (69.1 * (tblAll_Zips_Lat_Long.longitude) - ( [Enter Zip Code].Longitude) * cos(tblAll_Zips_Lat_Long.Latitude / 57.3)
) ^ 2
+ (69.1 * (tblAll_Zips_Lat_Long - [Enter Zip Code].Latitude)
) ^ 2
< 50 ^ 2

what does "fix" mean?

are you getting a syntax error? any kind of message?

or does the query run but produce no output?

have you tried displaying the terms in the SELECT for all rows?

I can't figure out how to access the parameter zip code's latitude and longitude. If I try [Enter Zip Code].Longitude it wants another parameter.

use [latitude] and [longitude], not [foo].latitude and [foo].longitude

it still doesn't work. what is [foo] mean? i've tried [Enter Zip Code].[Longitude], i've tried [Enter Zip Code].Longitude, and i've tried [Longitude]. i'm getting so many parameter entry boxes that i don't want.

here is my code:

SELECT *
FROM tblCustomer2
WHERE (69.1 *(tblAll_Zips_Lat_Long.[Longitude] - [Enter Zip Code].[Longitude]) * cos((tblAll_Zips_Lat_Long.[Latitude]) / 57.3)
) ^ 2
+ (69.1 * (tblAll_Zips_Lat_Long.Latitude -[Enter Zip Code].[Latitude])
) ^ 2
< 50 ^ 2;

see, that's the problem with table names that have to be qualified, i can't tell which is a table name and which is a user prompt

something like this perhaps?


now the only problem you might have is reconciling the FROM table of tblCustomer2 with the qualifying tablename tblAll_Zips_Lat_Long which is apparently not defined anywhere...

I ended up getting it to work a different way.

SELECT tblCustomer2.*, Sqr((69.1*([tblAll_Zips_Lat_Long].[Longitude]-(SELECT tblAll_Zips_Lat_Long.Longitude FROM tblAll_Zips_Lat_Long WHERE [tblAll_Zips_Lat_Long].[ZipCode] = [Enter Zip Code]))*Cos(([tblAll_Zips_Lat_Long].[Latitude])/57.3))^2+(69.1*([tblAll_Zips_Lat_Long].[Latitude]-(SELECT tblAll_Zips_Lat_Long.Latitude FROM tblAll_Zips_Lat_Long WHERE [tblAll_Zips_Lat_Long].[ZipCode] = [Enter Zip Code])))^2) AS Distance
FROM tblAll_Zips_Lat_Long INNER JOIN tblCustomer2 ON tblAll_Zips_Lat_Long.ZipCode = tblCustomer2.ZipCode
WHERE ((((69.1*([tblAll_Zips_Lat_Long].[Longitude]-(SELECT tblAll_Zips_Lat_Long.Longitude FROM tblAll_Zips_Lat_Long WHERE [tblAll_Zips_Lat_Long].[ZipCode] = [Enter Zip Code]))*Cos(([tblAll_Zips_Lat_Long].[Latitude])/57.3))^2+(69.1*([tblAll_Zips_Lat_Long].[Latitude]-(SELECT tblAll_Zips_Lat_Long.Latitude FROM tblAll_Zips_Lat_Long WHERE [tblAll_Zips_Lat_Long].[ZipCode] = [Enter Zip Code])))^2)<[Enter distance in miles]^2));

How can i Order By Distance??
Every time i try it it gives me a syntax error.
Thanks for your help