Microsoft Access Statistical Functions using VBA in Total Access Statistics

Microsoft Access has limited numerical analysis features. For more advanced number crunching, the MS Access community has relied on Total Access Statistics for almost two decades.

With a wide range of statistical functions, Total Access Statistics makes it easy to generate the results you need. Best of all, the output is placed in Access tables which you can use in your queries, forms, and reports.

Interactive Wizard

Total Access Statistics includes an Access add-in that lets you analyze your data through a Wizard that guides you through the process to select the data source, fields, and analysis without programming. After you specify your selections, they are automatically saved as a scenario in your database so you can run them again in the future when your data changes.

No Complicated VBA Functions

Total Access Statistics makes it very easy to add and automate complex statistical analysis into your application. Rather than creating a complex language for each statistical function and all its options, Total Access Statistics lets you specify those options from the Interactive Wizard. The VBA interface simply runs the settings you've saved. This makes it very easy for you to specify, run, and test the analysis before adding to your project.

Each saved scenario has a unique number and you call the statistics function by passing the ID of the scenario you want to run. It couldn't be easier!

VBA Programmatic Interface

A VBA programmatic interface is available so you can generate your statistical calculations from code. You can easily run any saved scenario by calling a single procedure in the Total Access Statistics VBA library. The library includes a royalty-free runtime license so you can include it with your Access database and distribute it to non-Total Access Statistics owners.

Call the function from a button's OnClick event or other process, and the results are generated for you. You can even hide the built-in user interface so your users don't even know Total Access Statistics is running. They'll be amazed with your statistical analysis capabilities!

Add Total Access Statistics to Your References

To use a function in a library database, you need to create an explicit reference from your database to the library. Handle this by putting a module in design mode, and invoking the VBA Editor’s Tools, References menu:

Different runtime libraries are available to support your Access version and database format. If you are using a MDB database, a MDE runtime library is used. If you are using an ACCDB database in Access 2007 or 2010, an ACCDE database is available. A 64 bit version of the ACCDE is available for Access 2010, 64 bit version.

Statistics Function: TAS_Statistics

Total Access Statistics includes a special VBA function (TAS_Statistics) for your use in the runtime library. To add this to your code, follow these easy steps:

• Create the scenario in the database with the data to analyze.
• Determine the scenario number of the scenario desired.
• Make sure Total Access Statistics is properly installed and referenced by your database as a library database.
• Invoke the Total Access Statistics function.

The VBA statistics function is:

Function TAS_Statistics(
  ByVal lngScenarioNo As Long, 
  ByVal strScenarioTbl As String, 
  ByVal strFieldTbl As String,
  ByVal strOptionsTbl As String, 
  ByVal fMessage As Boolean,
  Optional strDataSource As String = "",
  Optional strOutput1Tbl As String = "", 
  Optional strOutput2Tbl As String = "", 
  Optional strMessageTitle As String = "",
  Optional strParameterTbl As String = "")
As Boolean

That may seem complicated, but most of the parameters are optional. Here's an example of run a saved scenario (number 5) without any changes:

Dim fOK As Boolean

fOK = TAS_Statistics(5, "", "", "", True)

The function returns a True or False depending on whether it was successful.

Code Generator

To make it even easier to create the VBA code for your application, Total Access Statistics includes a Code Generator feature that creates a procedure for your currently selected scenario:

Simply paste it in your application and you're ready to go.

Additional Resources

For more information on the programmatic interface, the TAS_Statistics parameter definitions, and all the features of Total Access Statistics, download the Total Access Statistics User Manual for Microsoft Access.

Main Total Access Statistics page