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.
Microsoft Access is a fully capable database and it's primary limitations is the number of concurrent users it will support. Generally, we recommend a limit of 10 to 15 users.
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
doubledeclining 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 (doubledeclining 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.98340788992076E02