How to use advanced (Excel) function within SQL in FrontPage

[paigenoel]

I want to resuse some of this formula in FrontPage database results to select certain data from my Excel spreadsheet.

I have an MS Excel function which works in excel:
=3443.917*ACOS(COS(RADIANS(90-38.556216))*COS(RADIANS(90-38.3147))+
SIN(RADIANS(90-38.556216))*SIN(RADIANS(90-38.3147))*COS(RADIANS(90.59731-90.1758)))

This formula calculates the distance between 2 sets of lats and longs. I am trying to locate records based on Lat and Longs which are only 25 miles from my home. I know the lat and long for my home and store that in a variable.
The spreadsheet contains lats and longs for many other sites/address and I need to find the ones within a certain distance from my home. I would replace the hardcoded values with the names used in excel for the columns. When I attempt this formula in the FrontPage database results wizard I get an error that the ACOS function isn't recoginzed or supported.

To set this up I registered my excel spreadsheet as my datasource and then selected the wizard to create "database results". I can locate the excel sheet in question and then attempt to customize the result set. I know that the connection is successful because if I have no criteria or SQL selected then I get all of the records returned to me.

How can I found out what functions are supported? and how can I add functions which I need?

 

[xwb]

Don't use the wizard: just code it directly.

Either way, you need to locate a program called either MSE.exe or MSE7.exe. Alternatively in Frontpage, Tools/Macros/Microsoft Script editor. If it hasn't been installed, it will automatically install and launch.

From there, click on help/Microsoft Script Editor Help. If you're using Javascript, under contents, look for JScript/JScript Language Reference/Methods. If you're using VBScript, under contents, look for VBScript/VBScript Language Reference/Functions.

As far as I know, there is no RADIANS function in either of them so you'll have to divide by 180 and multiply by 3.141926535. All JScript math functions need a Math. in front of them. VBScript doesn't have acos but you can work it out using atn. Something like

acos(x)=atn(sqrt(1-x*x) / x)
asin(x)=atn(x /sqrt(1-x*x))}

With a special case for 0 in acos and 1 and asin. It is a pain but hardly anyone uses these math functions.