Functions

Functions return values to their names; functions can take the place of identifiers in expressions. One of the most common functions used in VBA/Jet expressions is Now, which returns to its name the date and time from your computer's system clock. If you type Now as the Default Value property of a table's Date/Time field, for example, 3/15/2003 9:00 appears in the field when you change to Datasheet view (at 9:00 a.m. on March 15, 2003).

VBA defines about 150 individual functions. The following list groups functions by purpose:

• Date and time functions manipulate date/time values in fields or Date/Time values that you enter as literals. You can extract parts of dates (such as the year or day of the month) and parts of times (such as hours and minutes) with date and time functions.

• Text-manipulation functions are used for working with strings of characters.

• Data-type conversion functions enable you to specify the data type of values in numeric fields instead of depending on Access to pick the most appropriate data type.

• Mathematic and trigonometric functions perform on numeric values operations that are beyond the capability of the standard Access arithmetic operators. You can use simple trigonometric functions, for example, to calculate the length of the sides of a right triangle (if you know the length of one side and the included angle).

• Financial functions are similar to functions provided by Lotus 1-2-3 and Microsoft Excel. They calculate depreciation, values of annuities, and rates of return on investments. To determine the present value of a lottery prize paid out in 25 equal yearly installments, for example, you can use the PV function.

• General-purpose functions don't fit any of the preceding classifications; you use these functions to create Access queries, forms, and reports.

• Other functions include those that perform dynamic data exchange (DDE) with other Windows applications, domain aggregate functions, Jet SQL aggregate functions, and functions used primarily in VBA programming.

Only the first three groups of functions commonly are used in Jet queries; Chapters 27 through 31 offer examples of the use of some of members of the last four function groups.

Using the Immediate Window

When you write VBA programming code in a module, the Immediate window is available to assist you in debugging your code. You also can use the module's Immediate window to demonstrate the use and syntax of functions.

To experiment with some of the functions described in the following sections, open the Northwind.mdb database and perform these steps:

1. Click the Modules shortcut in the Database window.

2. Double-click the Utility Functions module to open it in the VBA Editor. If you haven't changed the docking options for the VBA Editor, the Immediate window appears at the bottom of the Editor's window. Otherwise, press Ctrl+G to open the Immediate window.

3. Type ? Now in the Immediate window (see Figure 10.1) and press Enter. The date and time from your computer's clock appear on the next line. The ? is shorthand for the VBA Print statement (which displays the value of a function or variable) and must be added to the Now function to display the function's value.

   Figure 10.1. The VBA editor's Immediate window lets you quickly test the return values of VBA functions.

   

   Tip

   If you neglected to precede the function entry with ? or Print, an error message appears, indicating that the VBA editor expected you to type a statement or an equal sign. Click OK and type ? before the function name in the Immediate window. Press End to return the cursor to the end of the line and then press Enter to retry the test.

4. To reposition the Immediate window more easily, click its title bar and drag the window to a central area of your display where it remains undocked.

Getting Help as You Write Queries

As you type in your functions in the Immediate window, Access displays an Autocompletion screen tip, showing the function's name and its complete argument list. You must type a space or opening parenthesis after the function name to make the Autocompletion screen tip appear. An argument list is the list of information that you specify for the function to work on for example, if you use the Sqr function to compute the square root of a number, you must supply a number inside the function's parentheses. Figure 10.2 shows the screen tip for the Sqr function. You can turn this feature on and off by choosing Tools, Options and marking or clearing the Auto Quick Info option on the Editor page.

Figure 10.2. Typing a function in the Immediate window displays the VBA Autocompletion screen tip before you add required arguments for the function.

Tip

Obtain online help for a function by placing the cursor within the function name and pressing F1 to display the Visual Basic Reference topic for the function (see Figure 10.3).

Figure 10.3. Positioning the cursor within the name of a function and pressing F1 opens the Microsoft Visual Basic Help window and displays the online help topic for the function.

Note

If you specified Install on First Use, rather than Run from My Computer, for Access and VBA help files, you receive an "Installing components for Microsoft Access" message. In this case, you must have the distribution CD-ROM available or have access to a networked Office 2002 installation share to install the required HTML help files.

If you click an enabled Example link in any function Help window, the window displays an example of the function used in Access VBA code. These examples show the syntax of the functions and appropriate arguments. The examples, however, usually aren't applicable to the function's use in a Jet query or validation rule. Figure 10.4 illustrates the examples for the VBA Format function.

Figure 10.4. Most examples of VBA function syntax apply to writing VBA code, not formatting the columns of Jet query result sets.

The Variant Data Type in Jet and VBA

Variant is a special data type unique to Visual Basic dialects. The Variant data type enables you to concatenate values that ordinarily have different data types, such as an integer and a character string, which otherwise would result in an error. The capability to concatenate different data types is called turning off data-type checking. The Variant data type also lets you use operands with data of different types, such as adding Integer and Double values. Internally, Jet handles all data in tables and queries as Variant data.

The Variant data type enables you to concatenate field values of tables and queries that have dissimilar data types without using VBA's data-type conversion functions such as Str. (Str converts numeric values to the String data type.) The Variant data type simplifies expressions that combine field values to create concatenated indexes. The Variant data type also enables you to use the & symbol to concatenate values of different data types.

Table 10.5 lists the 16 common subtypes of the Variant data type of VBA 6.0, along with the names of the intrinsic Visual Basic constants, vbConstant, corresponding to the Variant subtype value. In addition to the Access intrinsic constants, VBA provides its own set of intrinsic constants, which are prefixed with vb. Access intrinsic constants are prefixed with ac. Intrinsic constants, which you use primarily when writing VBA code, are one of the subjects of Chapter 30, "Understanding Universal Data Access, OLE DB, and ADO."

Table 10.5. Subtypes of the Variant Data Type

Subtype	Constant	Corresponds To	Stored As
0	(None)	Empty (uninitialized)	Not applicable
1	vbNull	Null (no valid data)	Not applicable
2	vbInteger	Integer	2-byte integer
3	vbLong	Long	4-byte long integer
4	vbSingle	Single	4-byte single-precision floating point
5	vbDouble	Double	8-byte double-precision floating point
6	vbCurrency	Currency	4-byte fixed point
7	vbDate	Date/Time	8-byte double-precision floating point
8	vbString	String	Conventional string variable
9	vbObject	Object	Automation object
10	vbError	Error	Error data type (error number)
11	vbBoolean	Boolean	True or False values only
12	vbVariant	Variant	Used with Variant arrays
13	vbDataObject	Special	Non-Automation object
17	vbByte	Byte	Numeric value from 0 255
8192	vbArray	Array	Used with Variant arrays

You can concatenate Variant values with Variant subtypes 1 through 8 listed in Table 10.5. You can concatenate a subtype 8 Variant (String) with a subtype 5 Variant(Double), for example, without receiving the Type Mismatch error message displayed when you attempt this concatenation with conventional String (text) and Double data types. Access returns a value with the Variant subtype corresponding to the highest subtype number of the concatenated values. This example, therefore, returns a subtype 8 (String) Variant because 8 is greater than 5, the subtype number for the Double value. If you concatenate a subtype 2 (Integer) value with a subtype 3 (Long) value, Access returns subtype 3 Variant data.

T-SQL

SQL Server 2000 supports the sql_variant data type, which can store any SQL Server data type except text (Memo), ntext (Unicode or national text), image (long binary), timestamp, and sql_variant data. Concatenation rules for sql_variant values differ from Jet/VBA Variants.

Distinguishing between the empty and Null Variant subtypes is important. Empty indicates that a variable you created with VBA code has a name but doesn't have an initial value. Empty applies only to VBA variables (see Chapter 27, "Learning Visual Basic for Applications"). Null indicates that a data cell doesn't contain an entry. You can assign the Null value to a variable, in which case the variable is initialized to the Null value, Variant subtype 1.

Functions for Date and Time

Access offers a variety of functions for dealing with dates and times. If you've used Visual Basic, you probably recognize most of the functions applicable to the Date/Time field data types shown in Table 10.6. VBA has several Date/Time functions, such as DateAdd and DateDiff, to simplify the calculation of date values. MonthName and WeekdayName functions are new to VBA 6.0.

Table 10.6. Access Functions For Date And Time

Function	Description	Example	Returns
Date	Returns the current system date and time as a subtype7 date Variant or a standard date String subtype 8.	Date	3/15/2003 03-15-2003
DateAdd	Returns a subtype 7 date with a specified number of days ("d"), weeks ("ww"), months ("m"), or years ("y") added to the date.
DateAdd("d",31,#3/15/2003#),	4/15/2003
DateDiff	Returns an Integer representing the difference between two dates using the d/w/m/y specification	DateDiff("d", Date,#3/15/2003#)	116 (assuming Date = 11/19/2002)
DatePart	Returns the specified part of a date such as day, month,year, day of week ("w"), and so on, as an Integer	DatePart("w", #3/17/2003#)	2 (Monday)
DateSerial	Returns a subtype 7 Variant from year, month, and day arguments.	DateSerial (2003, 3, 15)	3/15/2003
DateValue	Returns a subtype 7 Variant that corresponds to a date argument in a character format.	DateValue ("15-Mar-2003")	3/15/2003
Day	Returns an Integer between 1 and 31 (inclusive) that represents a day of the month from a Date/Time value.	Day(Date)	15(assuming that the date is the 15th of the month)
Hour	Returns an Integer between 0 and 23 (inclusive) that represents the hour of the Date/Time value.	Hour(#2:30 PM#)	14
Minute	Returns an Integer between 0 and 59 (inclusive) that represents the minute of a Date/Time value.	Minute(#2:30 PM#)	30
Month	Returns an Integer between 1 and 12 (inclusive) that represents the month of a Date/Time value.	Month(#15-Jul-98#)	7
MonthName	Returns the full or abbreviated name of a month from the month number (1 to 12).If you omit the second argument, the function returns the full name.	MonthName(10, False) MonthName(10, True)	October Oct
Now	Returns the date and time of a computer's system clock as a Variant of subtype 7	Now	3/15/2003 11:57:28 AM .
Second	Returns an Integer between 0 and 59 (inclusive) that represents the second of a Date/Time value.	Second(Now)	28
Time	Returns the time portion of a Date/Time value from the system clock.	Time	11:57:20 AM
TimeSerial	Returns the time serial value of the time expressed in hours,minutes, and seconds.	TimeSerial(11, 57,20)	11:57:20 AM
TimeValue	Returns the time serial value of the time (entered as the String value) as a subtype 7 Variant.	TimeValue("11:57")	11:57
Weekday	Returns day of the week (Sunday = 1) corresponding to the date as an Integer.	Weekday(#3/15/2003#)	7
WeekdayName	Returns the full or abbreviated name of the day from the day number (0 to 7). Setting the second argument to True abbreviates the name. A third optional argument lets you specify the first day of the week.	WeekdayName(4, False) WeekdayName(4, True)	Wednesday Wed
Year	Returns the year of a Date/Time value as an Integer.	Year(#3/15/2003#)	2003

T-SQL

When you use the Upsizing Wizard to convert a conventional Access application with a Jet database to an Access Data Project with an SQL Server database, the Wizard converts the following Jet/VBA Date/Time functions to their T-SQL equivalents:

Jet/VBA Function	SQL Server Function
Date	CONVERT(datetime, CONVERT(varchar, GETDATE( ))
DateAdd()	DATEADD()
DateDiff()	DATEDIFF()
DatePart()	DATEPART()
Day()	DATEPART(dd, date)
Hour()	DATEPART(hh, time)
Minute()	DATEPART(mi, time)
Now	GETDATE( )
Second( )	DATEPART(ss, time)
Weekday()	DATEPART(dw, date)
Year()	DATEPART(yy, date)

The Wizard doesn't convert Jet/VBA Functions not included in the preceding list. You must manually correct conversion failures.

Text-Manipulation Functions

Table 10.7 lists the functions that deal with the Text field data type, corresponding to the String VBA data type. Most of these functions are modeled on BASIC string functions.

Table 10.7. Functions for the String Data Type

Function	Description	Example	Returns
Asc	Returns ANSI numeric value of a character as an Integer	Asc("C")	67
Chr	Returns character corresponding to the numeric ANSI value as a String	Chr(67) Chr(10)	C(line feed)
Format	Formats an expression in accordance with appropriate format strings	Format(Date, "dd-mmm-yyyy")	15-Mar-2003
InStr	Returns the position Long of one string within another as a Long	InStr("ABCD", "C")	3
InStrRev	Returns the position of one string within another as a Long, counting from the end of the string	InStrRev("ABCD", "C")	2
Join	Generates a String froma one-dimension array consisting of strings (spaces separate the array strings)	Join(astrArray)	Depends on array's contents
LCase	Returns the lowercase version of a string	LCase("ABCD")	abcd
Left	Returns the leftmost characters of a string	Left("ABCDEF", 3)	ABC
Len	Returns the number of characters in a string as a Long	Len("ABCDE")	5
LTrim	Removes leading spaces from a string	LTrim(" ABC")	ABC
Mid	Returns a part of a string, beginning at the character position specified by the second argument	Mid("ABCDE", 2, 3)	BCD
Replace	Replaces occurrences of a specified substring in a string	Replace("ABCDE","BC", "YZ")	AYZDE
Right	Returns the rightmost characters of a string	Right("ABCDEF", 3)	DEF
RTrim	Removes trailing spaces from a string	RTrim("ABC ")	ABC
Space	Returns a string consisting of a specified number of spaces	Space(5)
Split	Returns an array of substrings based on a separator character (the default is a space)	Split("ABC DEF")	(0)ABC (1)DEF
Str	Converts the numeric value of any data type to a string	Str(123.45)	123.45
StrComp	Compares two strings for equivalence and returns the integer result of the comparison	StrComp("ABC","abc")	0
String	Returns a string consisting of specified repeated characters	String(5, "A")	AAAAA
StrReverse	Returns a string whose characters are reversed	StrReverse("ABCDE")	EDCBA
Trim	Removes leading and trailing spaces from a string	Trim(" ABC ")	ABC
UCase	Returns the uppercase version of a string	UCase("abc")	ABC
Val	Returns the numeric value of a string in a data type appropriate to the argument's format	Val("123.45")	123.45

Note

VBA includes two versions of many functions that return String variables one with and one without the BASIC-language $ String type identification character. This book doesn't use type identification characters in queries, so the second form of the function is omitted from the tables in this chapter. In VBA code, adding the $ suffix to functions that return strings gives slightly better performance.

T-SQL

The Upsizing Wizard converts the following Jet/VBA text-manipulation functions to their T-SQL equivalents:

Jet/VBA Function	SQL Server Function
Asc()	ASCII()
Chr()	CHAR()
LCase()	LOWER()
Len()	DATALENGTH()
LTrim()	LTRIM()
Mid()	SUBSTRING()
Right()	RIGHT()
RTrim()	RTRIM()
Space()	SPACE()
Str()	STR()
UCase()	UCASE()

Functions included in Table 10.7 but not in this list cause conversion errors. T-SQL doesn't support the Jet/VBA Format or Format... functions.

Figure 10.5 shows Immediate window examples of common string-manipulation functions. The Immediate window is particularly valuable for learning exactly how these functions behave with different types of literal values.

Figure 10.5. Use the Immediate window to verify the syntax of the VBA functions you plan to include in Jet queries or validation rules.

You can use the new localized Format... functions of VBA 6.0 in Jet 4.0 queries, if you provide numeric values for the functions' arguments. For example, you must substitute the numeric values shown for the vb... constants of the FormatDateTime function. Following is the syntax for the Format... functions:

• FormatCurrency(NumericValue[, DigitsAfterDecimal [, IncludeLeadingDigit [, ParensForNegativeNumbers [, GroupDigits]]]]) returns a value formatted with the localized currency symbol, including the Euro. With the exception of NumericValue, the arguments are optional. If IncludeLeadingDigit is True, fractional values are prefixed with $0 in North America. Setting GroupDigits to True applies the group delimiter, comma (as in $1,000) for North America.

• FormatDateTime(DateValue[, NamedFormat]) returns a date string whose format depends on the value of NamedFormat. Valid values of NamedFormat are vbGeneralDate (0), vbLongDate (1), vbShortDate (2), vbLongTime (3), or vbShortTime (4). Figure 10.6 illustrates the use of the FormatDateTime and other Format... functions.

  Figure 10.6. The Immediate window displays examples of the use of the VBA 6.0 Format... functions.

  

• FormatNumber(NumericValue[, DigitsAfterDecimal [, IncludeLeadingDigit [, ParensForNegativeNumbers [, GroupDigits]]]]) returns the same values as FormatCurrency, but without the currency symbol.

• FormatPercent(NumericValue[, DigitsAfterDecimal [, IncludeLeadingDigit [, ParensForNegativeNumbers [, GroupDigits]]]]) returns the same values as FormatNumber, but multiplies NumericValue by 100 and adds a trailing % symbol.

Numeric, Logical, Date/Time, and String Data-Type Conversion Functions

You can assign a particular data type to a numeric value with any of the data-type conversion functions. After you freeze (or coerce) a data type with one of the numeric data-type conversion functions, you cannot concatenate that data type with the String data type.

Table 10.8 lists the 11 numeric data-type conversion functions of Access 2003. The NumValue argument in the Syntax column can be any numeric or String value. However, if you use a String value as the argument of a numeric-type conversion function, the first character of the argument's value must be a digit, a dollar sign, a plus symbol, or a minus symbol. The most commonly used conversion function in queries is CCur.

Table 10.8. Data-Type Conversion Functions for Numeric, Time/Date, and String Values

Function	Syntax	Description
CBool	CBool(NumValue)	Converts a numeric value to the Boolean (True or False) data type
CByte	CByte(NumValue)	Converts a numeric value to the Byte (0 255) data type
CCur	CCur(NumValue)	Converts a numeric value to the Currency data type
CDate	CDate(NumValue)	Converts a numeric value to a Date value (CDate replaces CVDate, which is obsolete)
CDbl	CDbl(NumValue)	Converts a numeric value to the Double data type
CInt	CInt(NumValue)	Converts a numeric value to the Integer data type
CLng	CLng(NumValue)	Converts a numeric value to the Long integer data type
CSng	CSng(NumValue)	Converts a numeric value to the Single data type
CStr	CStr(varValue)	Converts a Variant value to the String data type
CVar	CVar(NumValue)	Converts a numeric value to a Variant data type
CVErr	CVErr(NumValue)	Converts a valid error number to create user-defined
Nz	Nz(varFieldValue[, ReturnValue]	Converts a Null value to 0 or a zero-length string, depending on the context of use

T-SQL

The Upsizing Wizard converts the following Jet/VBA data type conversion functions to their T-SQL equivalents:

Jet/VBA Function	SQL Server Function
CCur(NumValue)	CONVERT(money, NumValue)
CDbl(NumValue)	CONVERT(float, NumValue)
CInt(NumValue)	CONVERT(smallint, NumValue)
CLng(NumValue)	CONVERT(int, NumValue)
CSng(NumValue)	CONVERT(real, NumValue)
CStr(NumValue)	CONVERT(varchar, NumValue)
CDate(NumValue)	CONVERT(datetime, NumValue)
CVDate(NumValue)	CONVERT(datetime, NumValue)

Functions included in Table 10.8 but not in this list cause conversion errors.

The Nz (Null-to-zero) function accepts only a Variant varFieldValue argument. Nz returns non-Null Variant argument values unchanged. When used in a Jet query, Nz returns an empty string ("") for Null argument values, unless you specify 0 or another literal, such as "Null" as the value of the optional ReturnValue argument. The Jet expression service supplies the Nz function; it's not a VBA reserved word, so it doesn't appear in bold type.

Tip

Use Nz to format the result sets of your crosstab queries, replacing Null values with 0. When you execute a crosstab query such as quarterly product sales by region cells for products with no sales in a region for the quarter are empty. Empty cells might mislead management into believing information is missing. Applying the Nz function puts a 0 in empty cells, which eliminates the ambiguity.