Built-in Functions

Functions are predefined code that performs specific tasks or calculations. Often, you "plug in" values that interact with the function. For instance, the Sum() function has a predefined task—it adds the values you specify or reference. The arguments can be a literal value, a field or variable reference, or an expression that evaluates to one of the former.

Access supports two types of functions: built-in and user-defined. The built-in functions are predefined and come with Access. By predefined, we mean you can't alter or customize their purpose.

As a developer, you can create user-defined functions to accomplish unique tasks for specific applications. You'll use functions in criteria expressions (queries), calculated controls, VBA procedures, and SQL statements.

The main thing to remember about both types of functions is that both return a value instead of its name. You supply the function and any arguments it needs, but the function returns only the resulting value. For instance, if you execute a function via the Visual Basic Editor's (VBE) Immediate window, VBA returns only the results of the function, not the function itself, as shown in Figure 9.1. We used the Asc function to return the ASCII value of the lowercase letter c, which is 99.

Figure 9.1: The Asc function returns only the resulting value.

Note 

Throughout this section, we refer to both Access native (built-in) and Access project functions. In this context, Access project functions are the same as native SQL Server functions. Both Access native and native SQL Server functions can be available to you in an Access project. When working with a front-end object, such as a form or a report, use the native Access functions. When executing stored procedures, you must use the native SQL Server (and T-SQL) functions.

A comprehensive discussion of all the built-in functions or how to create a user-defined function is beyond the scope of this chapter. However, it's worth mentioning that the built-in functions come in several categories:

• String manipulation

• Conversion

• Date

• Mathematical

• Financial

The following sections list many Access/VBA functions and their Access project equivalents, but the examples are of the native Access functions. For more specific examples of functions for an Access project file, see Chapter 8; SQL Server and Project built-in functions are the same.

String Functions

A String function performs some type of process on a string variable, field, or value. When passing a literal string to the function, you must enclose the string in double-quotation delimiters in the form "string". The Access Project (T-SQL) equivalent delimiters are the single quotation mark—'string'.

Asc("character")

Returns an Integer—the ANSI numeric value of a passed string argument. The Access Project/T-SQL equivalent is ASCII('character'). For example, the following function returns the ASCII value of 97 for the lowercase letter "a":

=Asc("a")

Chr$(value)

Returns a value's corresponding ANSI value. CHAR(value) is the Access Project equivalent/T-SQL equivalent. The following function returns the lowercase character "a", which is the ANSI equivalent of the letter 97:

=Chr(97)

LCase$("string")

Returns the lowercase equivalent of a passed string. LOWER('string') is the Access Project equivalent. The following statement returns "abc":

LCase("ABC")

Len("string")

Returns a long integer that represents the number of characters in a passed string. DATALENGTH('string')is the Access Project equivalent. The following statement returns the value 3 as a long integer:

Len("abc")

Left("string",number)

Returns a parsed string of a determined length as specified by the number argument and beginning at the passed string's leftmost character. The Access Project equivalent is LEFT('string ',number). The following function returns the string "ab":

Left("abc",2)

LTrim$("string")

Removes leading spaces from a passed string and returns just the string. The Access Project equivalent is LTRIM('string'). The following function returns the string "abc".

LTrim$(" abc") 

Mid$("string",begin,number)

Returns a specified number of characters from a passed string, as determined by the number argument, beginning at the position represented by the begin argument. The Access Project equivalent is SUBSTRING('string ',begin, number). The following function returns the string "bcd":

Mid$("abcde",2,3)

Right$("string",number)

Returns a specific number of characters from a passed string, specified by the number argument and beginning with the rightmost character. The Access Project equivalent is RIGHT('string',number). The following statement returns the string "de":

Right$("abcde",2)

RTrim$("string")

Removes trailing spaces from a string and returns the passed string, excluding any trailing space characters. The Access Project equivalent is SPACE('string'). The following function removes the trailing spaces and returns just the string "abcde".

RTrim$("abcde ")

Space$(value)

Returns a specific number of space characters. The Access Project equivalent is SPACE(value). The following function returns the string " " (which consists of three space characters):

Space$(3)

Str$(value)

Converts the values of any numeric datatype to a string. The Access Project equivalent is STR(value). The following function returns the string value "123":

Str$(123)

StrComp("string1","string2")

Compares two strings and returns the result, either 0 or –1 as an integer. The Access Project equivalents are the SOUNDEX() and DIFFERENCE() functions. Although they're not interchangeable with StrComp, they're similar. The following function returns –1 because the two strings aren't identical:

StrComp("susan","suzanne")

UCase$("string")

Returns the passed string in uppercase letters. The Access Project equivalents are UCASE('string') and UPPER('string'). The following function returns the string "ABCDE":

UCase$("abcde") 

Replace("string","substring","new")

Replaces occurrences of a substring in a target string with a new string. (There is no Access Project equivalent.) The following function returns the string "black":

Replace("block","o","a")

Split("string",delimiter)

Returns a one-dimensional array of substrings from the passed string, in which each substring consists of the characters that occur between each specified delimiter. If the delimiter is omitted, the space character is assumed. The example code returns each substring, the result of the Split function, in the Immediate window. For instance, if "string" equals the string "Susan Harkins", and the delimiter is the space character, the code returns the two substrings, "Susan" and "Harkins":

Dim a As Variant  Dim i As Integer  a = Split("string","delimiter")  For i = 0 To UBound(a)     Debug.Print a(i)  Next i

StrReverse("string")

Reverses a passed string. The Access Project equivalent is REVERSE('string'). The following function returns the string "edcba":

StrReverse("abcde")

Val("string")

Returns the numeric value of string. There is no Access Project equivalent, but CAST('string' AS  datatype) is close. The following function returns the value 123:

Val("123abc")

Conversion Functions

Conversion functions change a passed argument's datatype. They're tricky to work with because not all datatypes should be converted, so make sure you fully understand the implications of a conversion before applying it.

CBool(value)

Converts any numeric or string value to a Boolean datatype. If the expression is 0, the result is False; any other value returns True. The Access Project function CONVERT(bit,value) isn't exactly the same, but it's close. For instance, the following procedure returns False if the passed value equals 0, but True when the passed value is any other value:

Function TestBoo(val As Variant)    TestBoo = CBool(val)    Debug.Print TestBoo  End Function 

CByte(value)

Converts a numeric value to the byte datatype. The passed value must be a value from 0 to 255; otherwise, the function returns an error. Use CONVERT(tinyint,value) in an Access Project. The following procedure returns the appropriate datatype of byte in the Immediate window only when the passed value is an appropriate value; otherwise, it returns an error message:

Function TestByte(val As Variant)    TestByte = CByte(val)    Debug.Print TypeName(TestByte)  End Function

CCur(value)

Converts a numeric or string value to the currency datatype. The following procedure returns the appropriate datatype of currency in the Immediate window if the passed value is also an appropriate currency value:

Function TestCurrency(val As Variant)    TestCurrency = CCur(val)    Debug.Print TypeName(TestCurrency)  End Function

The Access Project equivalent is CONVERT(money, value).

CDbl(value)

Converts a numeric value to the double datatype. If the value equals 37,950, the following function returns the date 11/25/2003.

Function TestDate(val As Variant)   TestDate = CDate(val)   Debug.Print TypeName(TestDate)  End Function

The Access Project equivalent is CONVERT(float, value).

Nz(variant,valueifnull)

Converts a Null value to 0, to a zero-length string, or to some other specified string value. There's no Access Project equivalent. An expression always returns Null if even one of the operands is Null. The following expression produces an error if either variant1 or variant2 is Null:

var = variant1 + variant2

To avoid the error, wrap both in an Nz() function as follows:

var = Nz(variant1) + Nz(variant2)

CInt(value)

Converts a numeric value to the integer datatype. The following procedure returns the appropriate datatype of integer in the Immediate window if the passed value is also an appropriate integer value:

Function TestInt(val As Variant)   TestInt = CInt(val)   Debug.Print TypeName(TestInt) End Function

The Access Project equivalent is CONVERT(smallint,value).

CLng(value)

Converts a numeric value to the long integer datatype. The following procedure returns the appropriate datatype of long integer in the Immediate window if the passed value is also an appropriate long integer value:

Function TestLng(val As Variant)   TestLng = CLng(val)   Debug.Print TypeName(TestLng) End Function

The Access Project equivalent is CONVERT(int ,value).

Sng(value)

Converts a numeric value to the single datatype. The following procedure returns the appropriate datatype of single in the Immediate window if the passed value is also an appropriate single- precision value:

Function TestSng(val As Variant)   TestSng = CSng(val)   Debug.Print TypeName(TestSng) End Function

The Access Project equivalent is CONVERT(real,value).

CStr(value)

Converts a variant value to the string datatype. The following procedure returns the appropriate datatype of string in the Immediate window if the passed value is also an appropriate string value:

Function TestStr(val As Variant)   TestStr = CStr(val)   Debug.Print TypeName(TestStr) End Function

The Access Project equivalent is CONVERT(varchar,value).

CDate(value)

Converts a numeric value to a date value. The following procedure returns the appropriate datatype of date in the Immediate window if the passed value is also an appropriate date value:

Function TestDouble(val As Variant)   TestDouble = CDbl(val)   Debug.Print TypeName(TestDouble) End Function

The Access Project equivalent is CONVERT(datetime,x).

Date Functions

Date functions use date arithmetic or serial values in their calculations. Fortunately, Access has a wide time period that it supports, from January 1, 100, to December 31, 9999. Each serial value represents a specific moment on any given day in the time period. For instance, the serial value for January 1, 100, is –657434, and the serial value for December 31, 9999, is 2958101. December 30, 1899, is known as an anchor date and has a serial value of 0. Any date that follows the anchor is a positive value; dates falling before the anchor are negative values.

Several Date functions accept an argument that denotes a specific interval. Table 9.5 lists and describes the constants that this argument accepts in Access and in an Access Project.

Table 9.5: INTERVAL OPTIONS FOR SOME DATE FUNCTIONS

Access	Access Project	Description
yyyy	yyyy, yy, year	Returns a valid 4-digit year value
y	y	Returns a value between 1 and 365, representing the date's position within the calendar year—between January 1 and December 31
m	mm, m, month	Returns the month as a value between 1 and 12
q	qq, q, quarter	Returns a value 1 through 4, denoting the quarter of the year
d	dd, d, day	Denotes day as a value between 1 and 31
w	dw	Denotes position of day within the week; a value between 1 and 7
ww	ww, wk	Returns a value between 1 and 52, which represents the week of the year that the date falls within
h	hh, hour	Denotes hours as a value between 1 and 23
n	mi, n, minute	Denotes minutes as a value between 1 and 59
s	ss, s, second	Denotes seconds as a value between 1 and 59

Date()

Returns the current system date and time as a string or variant value. The following function accepts no arguments and always returns the current date:

Date() 

The Access Project equivalent is GETDATE().

Dateadd("datepart",interval,#date#)

Adds a specific number of intervals, which can be days, weeks, months, or years, to a specified date and returns the resulting date as a variant. The following function adds three days to August 19, 2002, and returns August 22, 2002:

DateAdd("d",3,#8/19/2002#)

The Access Project equivalent is DATEADD("datepart", interval, 'date').

Datediff("datepart",interval,#date#)

The returned integer value represents the difference between two dates in days, weeks, months, or years. The following function returns the difference between the two dates of August 22, 2002, and August 19, 2002—which is –3:

DateDiff("d",#8/22/2002#,#8/19/2002#)

The result is a negative value because the function expresses the greater date first, implying the subtraction necessary to compute the dates. If you reverse the order of the dates, the function returns 3. The Access Project equivalent is DATEDIFF("datepart", interval, 'date').

Datepart("datepart",#date#)

Returns an integer, the specified part of a date, such as a day, month, year, or day of the week. The following function returns the value 22, the day component of the specified date:

DatePart("d",#8/22/2002#)

The Access Project equivalent is DATEPART('datepart', 'date').

DateSerial(year,month,day)

Returns a date string (variant datatype) based on the arguments. There's no Access Project equivalent. The following function returns the date string 8/22/2002:

DateSerial(2002,8,22) 

DateValue("datestring")

Returns a date (variant datatype) based on a string. The following function returns the date string 8/22/2002:

DateValue(#8/22/2002#)

Day(#date#)

Returns an integer value between 1 and 31 that represents a day of the month. The following function returns the day component 22 from the specified date:

Day(#8/22/2002#)

The Access Project equivalent is DATEPART('dd','date').

Hour(#date#)

Returns an integer value between 0 and 23 that represents an hour of the day. At 7:52 P.M., the following function returns the value 19:

Hour(Now)

The Access Project equivalent is DATEPART('hh','date').

Minute(#date#)

Returns an integer value between 0 and 59 that represents a minute. At 7:53 P.M., the following function returns the value 53:

Minute(Now)

The Access Project equivalent is DATEPART('mi','date').

Month(#date#)

Returns an integer value between 1 and 12 that represents a month. The following function returns the value 8:

Month(#8/22/2002#)

You can substitute the actual date with the Now() function, and the function will return the value that represents the current month. The Access Project equivalent is DATEPART('mm','date').

Now()

Returns, as a variant value, the date and time of a computer's system clock. The Access Project equivalent is GETDATE(). If the current date is August 22, 2002, the Now() function will return 8/22/2002.

Second(#date#)

Returns an integer value between 0 and 59 that represents a second. At 7:53:42 P.M., the following function returns the value 42:

Second(Now)

The Access Project equivalent is DATEPART('ss','date').

Weekday(#date#)

Returns an integer value between 1 and 7 that represents a day of the week (1 = Sunday, 2 = Monday, and so on). The following function returns the value 5:

Weekday(#8/22/2002#)

August 22, 2002 is a Thursday, the fifth day of the week if Sunday is the first day of the week. The Access Project equivalent is DATEPART('dw','date').

Year(#date#)

Returns an integer value that represents a year. For instance, the following function returns the value 2002:

Year(#8/10/2002#)

The Access Project equivalent is DATEPART('yy','date').

Timeserial(hour,minute,seconds)

Returns the serial value of the time, expressed as a time string (string or variant datatype). There's no Access Project equivalent. The following time function returns the time string 5:52:43 P.M.:

TimeSerial(17,52,43)

The 17 argument represents 5 P.M. in a 24-hour clock.

Timevalue("time")

Returns a serial value (variant datatype) that represents the time. For instance, the following function returns the time 2:24 P.M. as a time, not a string:

Timevalue("2:24PM")

The Access Project equivalent is Timevalue('time').

Mathematical Functions

A Mathematical function generally performs some type of calculation that involves numeric values and mathematical operators. You supply the operands in the form of arguments, and the function processes those values within the context of its purpose.

Int(value)

Returns the integer component of value. The following function returns just the integer portion, 345, of the value 345.67:

Int(345.67)

The Access Project equivalent is FLOOR(value).

Sgn(value)

Returns an integer (–1, 0, or 1) that indicates value's sign. The following functions return the values 1, –1, and 0, respectively:

Sgn(365) Sgn(-4) Sgn(0)

The Access Project equivalent is SIGN(value).

Rnd(value)

Returns a random value between 0 and 1 based on a seed value. The seed value determines the next random value. Think of it as a type of bookmark. For instance, the function Rnd(value) might return the value .01 or .9999999, or even any value between, depending on the current position of the seed value. The Access Project function, Rand(), also returns a random value, but behaves differently, so don't expect results to be the same.

Understanding the Rnd() Function

The Rnd() function accepts a single argument, and that argument substantially changes the results:u If value is greater than 0, Rnd() returns the next random value in sequence, based on the seed value.

• If value equals 0, Rnd() returns the most recently generated value. That means the function repeats an already returned value.

• If value is less than 0, Rnd() returns the same value until you execute the Randomize() function. TheRandomize() function resets the seed value.

Of equal importance is that a negative value resets the seed value to the previously generated random value in sequence, so be careful when using negative values with the Rnd() function. If you must and you don't want the impact of resetting the seed value, combine the Rnd() function with the Abs() function in the following form:Rnd(Abs(value))

Abs(value)

Returns the absolute value of value. ABS(value) is the Access Project equivalent. The following function returns the value 3:

Abs(-3) 

Domain Functions

Domain functions are a subset of Mathematical functions. These functions are known as Aggregate or Domain functions. A domain is simply a set of records. For instance, you might want to sum or average all the values in a particular field. These functions are extremely flexible because you can specify criteria to limit the set. Don't confuse domain aggregates with SQL aggregates, which we'll review in the "Jet SQL" section later in this chapter. Domain functions don't have an Access Project equivalent—use Aggregate functions instead.

DSum(field,domain,criteria)

Calculates the sum of a set of values. The following function sums the price values for all the items in the current order if txtOrderID (most likely a text box control in a form) contains the OrderID value:

DSum("UnitPrice","[Order Details]","OrderID = txtOrderID") 

DCount(field,domain,criteria)

Counts the values in a set. The following function returns the number of items (not the quantity of each) in the current order if txtOrderID (most likely a text box control in a form) contains the OrderID value:

DCount("OrderID","[Order Details]","OrderID = txtOrderID")

BUILT-IN FUNCTIONS 205DStDev(field,domain,criteria)

Estimates the standard deviation across a set of values. The following function estimates the standard deviation for freight on orders shipped to the current shipping region in txtShipRegion:

DStDev("Freight","Orders","ShipRegion = txtShipRegion")

Note 

You may have noticed that some of the domain references are enclosed in brackets and some aren't. Access requires the brackets only when the name in question contains a space character, as is the case with the Order Details table. Some developers enclose all references in brackets to avoid forgetting the brackets when they're required.

DStDevP(field,domain,criteria)

Estimates the standard deviation across a population. To evaluate the population from the DStDev()

example, use the following function:

DStDevP("Freight","Orders","ShipRegion = txtShipRegion") 

DVar(field,domain,criteria)

Estimates variance across a set of values. The following function estimates the variance for freight on orders shipped to the current shipping region in txtShipRegion:

DVar("Freight","Orders","ShipRegion = txtShipRegion")

DVarP(field,domain,criteria)

Evaluates variance across a population. To evaluate the variance from the DVar() example across the population, use the following function:

DVarP("Freight","Orders","ShipRegion = txtShipRegion")

DMax(field,domain,criteria)

Determines the maximum value in a specified set of records. The following function returns the price for the highest-priced item in each order if txtOrderID (most likely a text box control on a form) contains the OrderID value:

DMax("UnitPrice","[Order Details]","OrderID = txtOrderID")

DMin(field,domain,criteria)

Determines the minimum value in a specified set of records. The following function returns the price for the lowest-priced item in each order if txtOrderID (most likely a text box control on a form) contains the OrderID value:

DMin("UnitPrice","[Order Details]","OrderID = txtOrderID")

DLookup(field,domain,criteria)

Finds a value in a particular field from a set of records. The following function returns the corresponding employee's name, based on the employee's identification number:

DLookup("LastName", "Employees", "EmployeeID = txtEmployeeID") 

DAvg(field,domain,criteria)

Calculates the average in a set of values in a specified set of records. The following function returns the average price for all the items in the current order if txtOrderID (most likely a text box control in a form) contains the OrderID value:

DAvg("UnitPrice","[Order Details]","OrderID = txtOrderID")

Aggregate Functions

Aggregate Functions are similar to Domain functions in that they consider a set of records. However, you can't limit an aggregate set based on criteria. The Aggregate function names and arguments are the same in both an Access MDB file and an Access Project. It's important to remember that Aggregate functions aren't native functions; they're provided by SQL. As a result, you can't use Aggregate functions with Visual Basic for Applications (VBA). To learn more about Aggregate functions, see the "SQL Aggregate Functions" section later in this chapter.

Financial Functions

A Financial function is a highly specialized type of Mathematical function that returns financial statistics. Use the Access native functions in an Access Project; there are no comparable native SQL Server functions.

IPmt(rate,payment,paymentperiods,presentvalue)

Calculates the interest payment for a given period of an annuity based on periodic, constant payments and a constant interest rate. For example, the following function returns a monthly interest payment of $37.50 on a $10,000 loan over 48 months at 4.5 percent annual interest:

IPmt(.045/12, 1, 48, -10000) 

Pmt(rate,paymentperiods,presentvalue,futurevalue,type)

Calculates the payment for an annuity based on periodic, constant payments and a constant interest rate. For example, the following function returns the monthly payment of $228 on a $10,000 loan over 48 months at 4.5 percent annual interest:

Pmt(.045/12, 48,-10000)

Note that futurevalue and type are optional arguments.

PPmt(rate,paymentperiodsrange,paymentperiods,presentvalue,futurevalue,type)

Calculates the principal payment for a given period of an annuity based on periodic, constant payments and a constant interest rate. For example, the following function returns the monthly principal payment of $191.00 on a $10,000 loan over 48 months at 4.5 percent annual interest:

PPmt(.045/12,1,48,-10000)  

NPer(rate,payment,presentvalue,futurevalue,type)

Calculates the number of periods for an annuity based on periodic, constant payments and a constant interest rate. For example, the following function returns the number of payments (48) at $228 that are necessary to pay off principal and interest on a $10,000 loan at 4.5 percent annual interest:

NPer(.045/12,228,-10000) 

Ddb(cost,salvage,life,period,factor)

Calculates the depreciation of an asset for a specific period using the double-declining balance method or other method (which you specify). For example, the following function calculates the depreciation during the third year, which is $500, for a $10,000 asset that will depreciate by $8,000 over four years:

Ddb(10000,2000,4,3) 

Syd(cost,salvage,life,period)

Calculates the sum-of-years digits depreciation of an asset for a specified period. For example, the following function calculates the sum-of-years' depreciation during the third year, which is $1,600, for a $10,000 asset that will depreciate by $8,000 over four years:

Syd(10000,2000,4,3) 

Fv(rate,paymentperiods,payment,presentvalue,type)

Calculates the future value of an annuity based on periodic, constant payments and a constant interest rate. For example, the following function returns the future value of $11,966 for an investment of $228 made every month for 48 months earning 4.5 percent annual interest:

Fv(.045/12,48,-228) 

Pv(rate,paymentperiods,payment,futurevalue,type)

Calculates the present value of an annuity based on periodic, constant payments to be paid in the future and a constant interest rate. For example, the following function returns the present value of $9,999 for an investment of $228 made every month for 48 months paying 4.5 percent annual interest:

Pv(.045/12,48,-228) 

Rate(paymentperiods,payment,presentvalue,futurevalue,type,guess)

Calculates the interest rate per period for an annuity. For example, the following function returns a 3.74 percent rate per payment period for a loan of $10,000 with a monthly payment of $228 every month for 48 months:

Rate(48,-228,10000)