Access Query Imported into SQL Server

[lewis33]

Hi, I am working with someone that imports my SQL from Access (and the tables) in SQL Server (7.0 I think). We are working on some queries that measure distance. Basically uses a zip code table and a provider table. The query pulls in the zip code parameter and compares to all records in the provider table and calculates the mileage. For some reason, it doesn't seem to work in SQL Server for my contact.
I basically add the latitude and longitude plus the (pi times the lat and long)radius longitude and radius latitude (lonrad and latrad) to each table and then the calculated fields (trig functions) should follow. Please look at code and let me know if there may be any reason this would not import, compile or run. THANK YOU. Runs fine in Access.

SELECT DISTINCT [PROVIDER TABLE].[Prov Name], [PROVIDER TABLE].ADDR, [PROVIDER TABLE].CITY, [PROVIDER TABLE].COUNTY_NAME, [PROVIDER TABLE].State, [PROVIDER TABLEP].Zip, [PROVIDER TABLE].PHONE, [PROVIDER TABLE]. [PROVIDER TABLE].[LatRad 2], [PROVIDER TABLE].[LonRad 2], [ZIP TABLE].[LatRad 1], [ZIP TABLE].[LonRad 1], [ZIP TABLE].ZIP_CD,
Abs([LonRad 1]-[PROVIDER TABLE]![LonRad 2]) AS LonRadDif, Sin([LatRad 1])*Sin([PROVIDER TABLE]![LatRad 2])+Cos([LatRad 1])*Cos([PROVIDER TABLE]![LatRad 2])*Cos([LonRadDif]) AS x, Atn(-[x]/Sqr(-[x]*[x]+1))+2*Atn(1) AS RadDist, [RadDist]*3958.754 AS DistMI
FROM [ZIP TABLE], [PROVIDER TABLE]
WHERE (([ZIP TABLE].ZIP_CD)="44145&quot);

 

[rac2]

ATN() and SQR() are not functions in SQL Server 7. The name of the function for arc tangent is ATAN(), for squaring would be POWER(numeric_expr, 2), or numeric_expr * numeric_expr. Possibly you meant SQRT() which is the same.


([PROVIDER TABLE]![LatRad 2]) would need to be
([PROVIDER TABLE].[LatRad 2])

 

[lewis33]

RAC2, thanks very, very much from beautiful Cleveland, OH.
That's a big help. I'll try as mentioned. I suspected that the arctangent was wrong. Thanks for the direction on the square root. Does the sin and cos translate as is? Thought this would be the same.