Have you ever needed to use trig functions in your database?

You may have noticed that although MS Access does offer basic trig functions:

• Sin
• Cos
• Tan
• Arctangent (atn)

it does not offer any of the advance, ‘Derived Math Functions’, such as:

• Secant
• Cosecant
• Cotangent
• Inverse Sine
• …
• Hyperbolic Sine
• …
• Inverse Hyperbolic Sine
• …

As a developer you have one of two options:

• Create, or find, a function to replicate these functions
• Utilize Excel’s trig functions from within you database

 

Create, or find, a function to replicate these functions

If you simply lookup the term ‘Derived Math Functions’ in the VBE’s help file you will find all the necessary information to build your own custom functions. That said, why not simply benefit from the fact that others before you have already done this work for you and simply perform a quick Google search to locate and existing module with these functions. For instance:

• http://www.access-programmers.co.uk/forums/showthread.php?p=485452
• http://blogannath.blogspot.com/2010/04/microsoft-access-tips-tricks-great.html

 

Utilize Excel’s trig functions from within you database

If you know that the database will be utilized on a computer that also has Excel installed on it, why not simply use it’s powerful library of trig function! Nothing could be easier to do. Simply use a procedure such as the one presented below

'---------------------------------------------------------------------------------------
' Procedure : Atanh
' Author    : CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Calculate the Inverse Hyperbolic Tangent by using Excel's built-in
'             function
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' x                 Value in Rads
'
' Usage Example:
' ~~~~~~~~~~~~~~~~
' Atanh(-0.9)       Gives -1.4722...
' Atanh(0)          Gives 0
'---------------------------------------------------------------------------------------
Function Atanh(x As Double) As Double
'This procedure requires a reference be set to the Microsoft Excel xx.x Library
On Error GoTo Error_Handler
    Dim oXls    As Excel.Application
     
    Set oXls = New Excel.Application
    Atanh = oXls.WorksheetFunction.Atanh(x)
     
Error_Handler_Exit:
    On Error Resume Next
    oXls.Quit
    Set oXls = Nothing
    Exit Function
    
Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: YourModuleName/ListDbTables" & vbCrLf & _
           "Error Description: " & Err.Description, vbCritical, _
           "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function