×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

(OP)
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?

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

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

CODE

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close