ODBC Scalar Functions

Microsoft Access SQL supports the use of the ODBC defined syntax for scalar functions. For example, the query:

SELECT DAILYCLOSE, DAILYCHANGE FROM DAILYQUOTE
WHERE {fn ABS(DAILYCHANGE)} > 5

Would return all rows where the absolute value of the change in the price of a stock was greater than five.

A subset of the ODBC defined scalar functions is supported. The following table lists the functions that are supported.

For a description of the arguments and a complete explanation of the escape syntax for including functions in a SQL statement, see the ODBC documentation.

String Functions

ASCII	LENGTH	RTRIM
CHAR	LOCATE	SPACE
CONCAT	LTRIM	SUBSTRING
LCASE	RIGHT	UCASE
LEFT

Numeric Functions

ABS	FLOOR	SIN
ATAN	LOG	SQRT
CEILING	POWER	TAN
COS	RAND	MOD
EXP	SIGN

Time & Date Functions

CURDATE	DAYOFYEAR	MONTH
CURTIME	YEAR	WEEK
NOW	HOUR	QUARTER
DAYOFMONTH	MINUTE	MONTHNAME
DAYOFWEEK	SECOND	DAYNAME

Data Type Conversion

CONVERT

String literals can be converted to the following data types: SQL_FLOAT, SQL_DOUBLE, SQL_NUMERIC, SQL_INTEGER, SQL_REAL, SQL_SMALLINT, SQL_VARCHAR and SQL_DATETIME.