Access Built-in Functions

[MMcCarthy]

Access has a number of built-in functions which can be generally used in queries or VBA code. Some of the more common ones are:

Note: anything in square brackets [] is optional

Date Functions

Date() - Returns the current system date

Now() - Returns the current system timestamp (date and time)

Year(dateValue) - Returns the 4 digit year of any given date

Month(dateValue) - Returns the month number of any given date

MonthName(number, [abreviate]) - Returns the monthname for any given number 1 - 12, putting true in the [abreviate section will abreviate the month name. The default is false. To get the month name of any given date this function would have to be used in conjunction with the previous function. e.g. MonthName(Month(dateValue))

Weekday(dateValue, [FirstDayofWeek]) - Returns the day of the week of any given date value 1-7. The first day of the week can be set e.g. vbMonday. The default is Sunday.

WeekdayName(number, [abbreviate], [firstdayofweek]) - Returns the weekday name of any given number. To get the weekday name of a given date this function can be used in conjunction with the previous one. e.g. WeekdayName(Weekday(dateValue))

Day(dateValue) - Returns the day of the month (1-31) of any given date value.

DateSerial(year, month, day) - Returns a date for any given year, month and day

Time() - Returns the current system time

Hour(timeValue) - Returns the hour value of any given time or timestamp

Minute(timeValue) - Returns the minute value of any given time or timestamp

Second(timeValue) - Returns the second value of any given time or timestamp

For the following functions you will need to know what intervals are:

 Value  Description
yyyy   Year
q      Quarter                                         
m      Month                                         
y      Day of the year                                         
d      Day                                         
w      Weekday                                         
ww     Week                                         
h      Hour                                         
n      Minute                                         
s      Second
 

DateAdd(interval, number, dateValue) - Returns the new date value after the addition of a number of specified intervals. e.g. DateAdd("yyyy", 3, #01/05/2001#) would return "01/05/2004"

DateDiff(interval, dateValue1, dateValue2, [firstdayofweek], [firstweekofyear]) - Returns the difference in the specified interval between two given dates. e.g. DateDiff("yyyy", #01/05/2001#, #02/07/2006#) would return 5. firstdayofweek is optional. It specifies the first day of the week. Access assumes that Sunday is the first day of the week. firstweekofyear is optional. It specifies the first week of the year. Access assumes that the week containing Jan 1st is the first week of the year.

DatePart(interval, dateValue, [firstdayofweek], [firstweekofyear]) - Returns the specified part of any given date. e.g. DatePart("yyyy", #02/07/2005#) would return 2005.

Number Functions

The following are normally used in SELECT queries known as aggregate queries. Please note if other fields are also returned by the query then the results will be grouped on those field values.

Avg(fieldName) - Returns the average of a series of values in a given field

Count(fieldName) - Returns the total number of records returned by a query

Sum(fieldName) - Returns the sum of a series of values in a given field

Max(fieldName) - Returns the largest of a series of values in a given field

Min(fieldName) - Returns the smallest of a series of values in a given field

The following can be used in queries or VBA code.

Int(numberValue) - Returns the integer part of any given number. Please note this will not round a number up to the nearest integer.

Round(numberValue, [decimal places) - Returns a number rounded to the specified number of decimal places. If the decimal places is left blank then the default is 0 which means an integer is returned. The Round function behaves a little unexpectedly as it uses round-to-even logic. If the number that you are rounding ends with a 5, the Round function will round the expression so that the last digit is an even number.
For example:
Round(22.35, 1) would return 12.4 (rounds up)
Round(22.65, 1) would return 12.6 (rounds down)
Round(22.75, 1) would return 12.8 (rounds up)

Val(stringValue) - Returns any numbers found in a string

String Functions

Instr([start position], StringBeingSearched, StringSearchedFor, [compare]) - Returns the position number in the string being searched of the string or character being searched for. The start position will default to position 1 (the start of the string). The compare is for the type of comparison to perform. The valid choices are vbUseCompareOption, vbBinaryCompare, vbTextCompare, and vbDatabaseCompare.

Len(StringValue) - Returns the number of characters in a given string. This will include spaces.

Left(stringValue, Number of characters) - Return a substring of the required length starting at the left of the string.

Right(stringValue, Number of characters) - Return a substring of the required length starting at the right of the string.

Mid(stringValue, Start Postion, Number of characters) - Return a substring of the required length starting at the given start position in the string.

Domain Aggregate Functions (functions which lookup values in a table or query)

DLookup (expression, domain, [criteria]) - Returns a single value found.In expression normally you would put a field name however it can also be a calculation. For domain you would put the table name or query name. Criteria is optional. Think of it as the WHERE clause. e.g. DLookup("OrderDate", "Orders", "OrderID = 10248")

DCount (expression, domain, [criteria]) - Returns the number of records in a table or query based on the criteria given. Criteria is optional.

DMax (expression, domain, [criteria]) - Returns the maximun value in the specified field.

DSum (expression, domain, [criteria]) - Returns the sum of values in a specified field or calculated expression.

Others

IIf (condition, value if true, value if false) - Returns whatever value is placed in Value if true if condition is true or whatever is in Value if false if conditon is false. In this case condition is the value you are testing. e.g. IIf(NumberField=0, "no value", NumberField)

IsNull(expression) - Returns true if the expression evaluates to Null and false if it doesn't.

Nz (variant, [value if null]) - Returns the value if null whenever the variant has a null value. The default for value if null is an empty string. Although this specifies a variant it will accept a value of any datatype and treat it as a variant.

[Coni]

Thank you for posting these useful functions.