Access 2010 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
VBA Functions for Access

VBA Cos Function

The Visual Basic Cos function takes an angle as an argument and returns the ratio of 2 sides of a right triangle (the Cosine). The ratio is the length of the side adjacent to the angle divided by the length of the hypotenuse.The result returned are in the range -1.0 to 1.0Example:

Dim TheAngle, TheSecant
TheAngle = 1.3                              ‘ Define angle in radians.
TheSecant = 1 / Cos(TheAngle)       ‘ Calculate secant.

The result of the Cos calculation is 0.26749….

VBA CreateObject Function

The Access Visual Basic CreateObject function creates and returns a reference value to an ActiveX object.  There are two arguments to the function:  Class & ServerName – class is a required argument that specifies the application name and class of the object to create.  The servername argument is optional and specifies the name of the network server where the object will be created. If servername is an empty string (“”), the local machine is used.

Example:

        ‘ Declare an object variable to hold the object
‘ reference. Dim as Object causes late binding ( see more info on late binding ).
Dim ExcelSheet As Object
Set ExcelSheet = CreateObject(“Excel.Sheet”) VBA CurDir FunctionThe Visual Basic CurDir function returns the current default path.Example:

Dim ThePath
ThePath = CurDir              ‘ Returns “C:WINDOWSSYSTEM”.
ThePath = CurDir(“C”)       ‘ Returns “C:WINDOWSSYSTEM”.
ThePath = CurDir(“D”)       ‘ Returns “D:EXCEL”.

VBA CVErr Function
The Access Visual Basic CVErr function gives you the ability to create your own custom error codes.  If you create a visual basic procedure or function you can use the CVErr feature to return an error code value to the calling procedure.

Example:

Function Figure_Age(BDate)
if IsDate(Bdate) then
‘ do the age calculations

Else
Figure_Age=CVErr(-999)
End if
End Function

VBA Date Function

The VB Date function returns a variant variable containing the current computer system date value.

Example:

Msgbox “Today’s Date is ” & Date

Result:  Today’s Date is 12/12/2006

VBA DateAdd Function

The Visual Basic DateAdd function allows you to add a time interval to a specified date.

Syntax:  DateAdd(interval, number, date)

The VB DateAdd function syntax has these named arguments:

interval  – Required argument. String expression that is the interval of time you want to add.

number – Required. Numeric expression that is the number of intervals you want to add. It can be positive (to get dates in the future) or negative (to get dates in the past).

date – Required. Variant (Date) or literal representing date to which the interval is added.

Here are the VB interval definitions:

Interval                                   Interval Description

Example usage:

MsgBox DateAdd(“d”, 13, “12/12/2006”)

Above VB dateadd examples displays ‘ 12/25/2006 ‘

VBA DateDiff Function(adsbygoogle = window.adsbygoogle || []).push({});

The VB DateDiff function returns a variant variable result of subtracting two dates.  The unit of measure is defined in the interval argument.

Syntax

DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])

Arguments:

interval – Required. String expression that is the interval of time you use to calculate the difference between date1 and date2. See a list of valid intervals in the DateAdd function above.

date1, date2 – Required; Variant (Date). Two dates you want to use in the calculation.

firstdayofweek – Optional. A constant that specifies the first day of the week. If not specified, Sunday is assumed.

firstweekofyear – Optional. A constant that specifies the first week of the year. If not specified, the first week is assumed to be the week in which January 1 occurs.

Example:

MsgBox DateDiff(“yyyy”, “01/01/2005”, “11/11/2006”)

Above displays ‘1’.

VBA DatePart Function

The VBA DatePart function give you the ability to extract part of a date constant of variable from a regular calendar date.  The part you select is called the interval and the intervals are the same as DateAdd function above.

See detailed information on the DatePart function in our Access SQL query examples – date/time query page.

VBA DateSerial Function

The Visual Basic DateSerial function returns the date (variant) for the specified year, month, and day.

Syntax:

DateSerial(year, month, day)

Where:

(All fields are required)

Year – Integer. Number between 100 and 9999, inclusive, or a numeric expression.

Month – Integer.  Two digit month between 1 and 12.

Day – Integer.  Two digit day between 1 and 31.

VBA DateValue Function

The VB DateValue Function converts a string date expression into a date value.

The syntax is DateValue(Date) where date is any standard representation of a date.

Below we use the DateValue function to convert a string to a date value. You can also use date constants or string literals to assign a date to a Variant or Date variable, for example, MyDate = #12/13/06#.  Not sure why this function is ever used.

Dim TheDate
TheDate = DateValue(“December 13, 2006”)        ‘ Converts to a date.
Access 2007
Access 2010
Access 2013
Access 2016
Office 365

 

Microsoft Office VBA, MS Access 2003, 2007, 2010, 2013, 2016