VB Functions for Access
VB Davg Function
Davg calculates the average for a set of values. See
our
Domain Aggregate Functions in MS
Access page for more information on domain aggregate
functions.
VB Day Function
The Visual Basic day function is pretty much identical to the datepart function
when specify 'd' as the part to be returned. The function returns a
variant day value between 1 and 31. Here is the syntax of the Day
function:
Day(date) where date is any valid date expression in Access Visual Basic.
VB Dcount Function
The VBA Dcount function determines the number of records that are in a specified
group of records (a domain). The DCount function can be used in macros, as query expressions, or a calculated controls.
You can use the DCount function in a module to return the number of records in
an Inventory table that correspond to inventory items added or removed on a
particular date.
Note: It is recommended that you do not used the Dcount function in queries
since it is much more efficient to use the Count function in SQL.
Syntax:
DCount(expr, domain, [criteria])
Where:
expr - A expression identifying the field you want to count
records. It can be a string identifying a field in a table, or it can be an
expression that performs a calculation on data in that field. In expr, you can
include the name of a field in a table, a control on a form, a constant, or a
function.
domain - A string expression identifying the set of records that
identifies the domain. Domain can be a table name or a query name for a query
that does not require a parameter.
criteria - An optional string expression used to limit the domain of data
on which the DCount function is calculated.
Example:
Dim mycount
mycount=Dcount("Part_ID","Inventory_Table","Part_ID=" & Me.Part_Combo)
VB DDB Function
The DDB VB function returns a double data type number specifying the
depreciation of an asset for a specific time period using the
double-declining balance method or some other method you specify.
Syntax:
DDB(cost, salvage, life, period[, factor])
Where:
cost - Required. Double initial cost of the asset.
salvage - Required. Double value of the asset at the end of its useful
life.
life - Required. Double length of useful life of the asset.
period - Required. Double period for which asset depreciation is
calculated.
factor -Optional. Variant rate at which the balance declines.
Default value of 2 (double-declining method).
VB DDE Function
DDE Function is used for Dynamic Data Exchange between two programs. You
can grab a piece of data from on application an display the value in an Access
control on a form or report.
Syntax:
DDE(application, topic, item)
The DDE function has the following arguments.
application - required string expression identifying an application that
can participate in a DDE conversation. Usually, application is the name of an
.exe file (without the .exe extension) for a Microsoft Windows–based
application, such as Microsoft Excel. For example, to initiate a DDE
conversation with Microsoft Excel, type "Excel" for the application argument.
topic - required string expression that is the name of a topic recognized
by application. The topic argument is often a document or data file. Check the
other application's documentation for a list of possible topics.
item A string expression that is the name of a data item recognized by
application. Check the other application's documentation for a list of possible
items.
Example:
Me.MyField==DDE("Excel", "Sheet1", "R1C1")
The above example retrieves the data with an Excel spreadsheet (Sheet1) at cell
location R1C1 and places it in a field 'MyField' on a form (or report)
VB DDEInitiate Function
The DDEInitiate function is used to begin a dynamic data exchange (DDE)
conversation with another application. The DDEInitiate function opens a DDE
channel for transfer of data between a DDE server and client application.
For example, if you wish to transfer data from a Microsoft Excel spreadsheet to
a Microsoft Access database, you can use the DDEInitiate function to open a
channel between the two applications. In this example, Microsoft Access acts as
the client application and Microsoft Excel acts as the server application.
Syntax:
DDEInitiate(application, topic)
Where:
application - string expression identifying an application that can
participate in a DDE conversation. Usually, the application argument is the name
of an .exe file (without the .exe extension) for a Microsoft Windows–based
application, such as Microsoft Excel.
topic - string expression that is the name of a topic recognized by the
application argument. Check the application's documentation for a list of
topics.
VB DDERequest Function
You can use the DDERequest function over an open dynamic data exchange (DDE)
channel to request an item of information from a DDE server application.
For example, if you have an open DDE channel between Microsoft Access and
Microsoft Excel, you can use the DDERequest function to transfer text from a
Microsoft Excel spreadsheet to a Microsoft Access database.
Syntax:
DDERequest(channum, item)
Where:
channum - channel number, the integer returned by the DDEInitiate
function.
item - string expression that's the name of a data item recognized by the
application specified by the DDEInitiate function. Check the application's
documentation for a list of possible items.
VB DEExecute Function
The VB DEExecute function gives you the ability to send a command to another
application. You must have already setup the channel to send the command,
see
DDEInitiate Function.
Syntax
DDEExecute(channum, command)
Where:
channum - required channel number, the long integer returned by the
DDEInitiate function.
command - required string expression specifying a command recognized by
the server application.
VB Derived Math Functions
| Derived Function |
|
| Secant |
Sec(X) = 1 / Cos(X) |
| Cosecant |
Cosec(X) = 1 / Sin(X) |
| Cotangent |
Cotan(X) = 1 / Tan(X) |
| Inverse Sine |
Arcsin(X) = Atn(X / Sqr(-X * X + 1)) |
| Inverse Cosine |
Arccos(X) = Atn(-X / Sqr(-X * X + 1)) + 2 * Atn(1) |
| Inverse Secant |
Arcsec(X) = Atn(X / Sqr(X * X – 1)) + Sgn((X) – 1)
* (2 * Atn(1)) |
| Inverse Cosecant |
Arccosec(X) = Atn(X / Sqr(X * X - 1)) + (Sgn(X) –
1) * (2 * Atn(1)) |
| Inverse Cotangent |
Arccotan(X) = Atn(X) + 2 * Atn(1) |
| Hyperbolic Sine |
HSin(X) = (Exp(X) – Exp(-X)) / 2 |
| Hyperbolic Cosine |
HCos(X) = (Exp(X) + Exp(-X)) / 2 |
| Hyperbolic Tangent |
HTan(X) = (Exp(X) - Exp(-X)) / (Exp(X) + Exp(-X)) |
| Hyperbolic Secant |
HSec(X) = 2 / (Exp(X) + Exp(-X)) |
| Hyperbolic Cosecant |
HCosec(X) = 2 / (Exp(X) - Exp(-X)) |
| Hyperbolic Cotangent |
HCotan(X) = (Exp(X) + Exp(-X)) / (Exp(X) -
Exp(-X)) |
| Inverse Hyperbolic Sine |
HArcsin(X) = Log(X + Sqr(X * X + 1)) |
| Inverse Hyperbolic Cosine |
HArccos(X) = Log(X + Sqr(X * X - 1)) |
| Inverse Hyperbolic Tangent |
HArctan(X) = Log((1 + X) / (1 - X)) / 2 |
| Inverse Hyperbolic Secant |
HArcsec(X) = Log((Sqr(-X * X + 1) + 1) / X) |
| Inverse Hyperbolic Cosecant |
HArccosec(X) = Log((Sgn(X) * Sqr(X * X + 1) + 1) /
X) |
| Inverse Hyperbolic Cotangent |
HArccotan(X) = Log((X + 1) / (X - 1)) / 2 |
| Logarithm to base N |
LogN(X) = Log(X) / Log(N) |
Example - Let's calculate the Inverse Hyperbolic Cosecant of a number:
Private Sub Command11_Click()
Dim X As Integer
X = 10
MsgBox Log((Sgn(X) * Sqr(X * X + 1) + 1) / X)
End Sub
The msgbox displays: 9.98340788992076E-02
