ODBC scalar functions

Applies to: Access 2013, Office 2013

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.