Access VB Functions
VBA Abs Function
VBA Array Function
VBA Asc Function
VBA Atn Function
VBA CallByName Function
VBA Choose Function
VBA Chr Function
VBA Command Function
VBA Conversion Function
VBA Cos Function
VBA CreateObject Function
VBA CurDir Function
VBA CVErr Function
VBA Date Function
VBA DateAdd Function
VBA DateDiff Function
VBA DatePart Function
VBA DateSerial Function
VBA DateValue Function
VBA Davg Function
VBA Day Function
VBA Dcount Function
VBA DDB Function
VBA DDE Function
VBA DDEInitiate Function
VBA DDE Request Function
VBA DEExecute Function
VBA Derived Math Functions
Microsoft Office VBA:
MS Access 2003
Access 2007
Access 2010
Access 2013
Dfirst,Dlast Functions
Dir Function
DoEvents Function
Domain Aggregate Functions
DSTDev,DSTDEVP Functions
Dsum Function
Dvar,DvarP Functions
Environ Function
EOF Function
Error Function
EuroConvert Function
Eval Function
Exp Function
FileAttr Function
FileDateTime Function
FileLen Function
Filter Function
Fix Function
Format Function
FormatCurrency Function
FormatDateTime Function
FreeFile Function
GetAllSettings Function
GetAttr Function
GetObject Function
GetSetting Function
Hex Function
Hour Function
IIF Function
IMEStatus Function
Input Function
InStr Function
InStrRev Function
Int Function
Ipmt Function
IRR Function
IsArray Function
IsDate Function
IsEmpty Function
IsNull Function
IsNumeric Function
IsObject Function
Join Function
Lbound Function
Lcase Function
Left Function
Len Function
Loc Function
LOF Function
Ltrim,Rtrim, Trim Functions
MacID Function
MacScript Function
Mid Function
Minute Function
MIRR Function
Month Function
MonthName Function
MsgBox Function
Now Function
Nper Function
Numeric Criteria
Nz Function
Oct Function
OpenFunction Method
Partition Function
Pmt Function
PPmt Function
PV Function
QBColor Function
Rate Function
Replace Function
ReplicationConflictFunction
Restrict Data to a Subset
RGB Function
Right Function
Rnd Function
Round Function
RowSourceType
Run Method
Second Function
Seek Function
Sgn Function
Shell Function
Sin Function
SLN Function
Space Function
Spc Function
Split Function
Sqr Function
Str Function
StrComp Function
StrConv Function
String Function
StringFromGUID Function
StrReverse Function
Switch Function
SYD Function
SysCmd Method
Tab Function
Tan Function
Text Criteria
Time Function
Timer Function
TimeSerial Function
TimeValue Function
Type Conversion Functions
TypeName Function
Ubound Function
Val Function
VarType Function
Weekday Function
WeekdayName Function
Year Function
VB Functions for Access
VB Davg Function
Davg calculates the average for a set of values. See ourDomain 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).(adsbygoogle = window.adsbygoogle || []).push({});
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
Microsoft Office VBA, MS Access 2003, 2007, 2010, 2013, 2016