Applies ToAccess for Microsoft 365 Access 2021 Access 2019 Access 2016 Access 2013

Microsoft Access SQL supports the use of the ODBC defined syntax for scalar functions in a pass-through query that runs on Microsoft SQL Server. For example, to return all rows where the absolute value of the change in the price of a stock was greater than five, use the following query:

SELECT DailyClose, DailyChange FROM DailyQuote
WHERE {fn ABS(DailyChange)} > 5

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

ODBC Scalar functions by category

A subset of the text functions are supported. The following table lists the ODBC Scalar functions and the equivalent Access functions if any.

ODBC Function (ODBC Version)

Access Function

ASCII (ODBC 1.0)

Asc Function

BIT_LENGTH (3.0)

(No equivalent)

CHAR ( ODBC 1.0)

Chr Function

CONCAT ( ODBC 1.0)

(Use the ampersand (&) character concatenation operator)

DIFFERENCE ( ODBC 2.0)

(No equivalent)

INSERT ( ODBC 1.0)

(No equivalent)

LCASE ( ODBC 1.0)

LCase Function

LEFT ( ODBC 1.0)

Left Function

LENGTH ( ODBC 1.0)

Len Function

LOCATE ( ODBC 1.0)

InStr Function

LTRIM ( ODBC 1.0)

LTrim, RTrim, and Trim Functions

OCTET_LENGTH ( ODBC 3.0) (No equivalent function)

(No equivalent)

POSITION ( ODBC 3.0)

InStr Function

REPEAT ( ODBC 1.0)

String Function

REPLACE ( ODBC 1.0)

Replace Function

RIGHT ( ODBC 1.0)

Right Function

RTRIM ( ODBC 1.0)

LTrim, RTrim, and Trim Functions

SOUNDEX ( ODBC 2.0)

(No equivalent)

SPACE ( ODBC 2.0)

Space Function

SUBSTRING ( ODBC 1.0)

Mid Function

UCASE ( ODBC 1.0)

UCase Function

A subset of the math functions are supported. The following table lists the ODBC Scalar functions and the equivalent Access functions if any.

ODBC Function (ODBC Version)

Access Function

ABS (ODBC 1.0)

Abs Function

ACOS (ODBC 1.0)

(No equivalent)

ASIN (ODBC 1.0)

(No equivalent)

CEILING (ODBC 1.0)

(No equivalent)

ATAN (ODBC 1.0)

Atn Function

ATAN2 (ODBC 2.0)

(No equivalent)

COS (ODBC 1.0)

Cos Function

COT (ODBC 1.0)

(No equivalent)

DEGREES (ODBC 2.0)

(No equivalent)

EXP (ODBC 1.0)

Exp Function

FLOOR (ODBC 1.0)

(No equivalent)

MOD (ODBC 1.0)

(Use the MOD operator)

LOG (ODBC 1.0)

Log Function

LOG10 (ODBC 2.0)

(No equivalent)

PI (ODBC 1.0)

(No equivalent)

POWER (ODBC 2.0)

(No equivalent)

RADIANS (ODBC 2.0)

(No equivalent)

RAND (ODBC 1.0)

Rnd Function

ROUND (ODBC 2.0)

Round Function

SIGN (ODBC 1.0)

Sgn Function

SIN (ODBC 1.0)

Sin Function

SQRT (ODBC 1.0)

Sqr Function

TAN (ODBC 1.0)

Tan Function

TRUNCATE (ODBC 2.0)

(No equivalent)

A subset of the date/time functions are supported. The following table lists the ODBC Scalar functions and the equivalent Access functions if any.

ODBC Function (ODBC Version)

Access Function

CURDATE (ODBC 1.0)

Date Function

CURTIME (ODBC 1.0)

Time Function

CURRENT_DATE (ODBC 3.0)

Date Function

CURRENT_TIME (ODBC 3.0)

Time Function

CURRENT_TIMESTAMP (ODBC 3.0)

Time Function

DAYNAME (ODBC 2.0)

WeekdayName Function

DAYOFMONTH (ODBC 1.0)

Day Function

DAYOFWEEK (ODBC 1.0)

Weekday Function

DAYOFYEAR (ODBC 1.0)

DatePart Function

EXTRACT (ODBC 3.0)

DatePart Function

HOUR (ODBC 1.0)

Hour Function

MINUTE (ODBC 1.0)

Minute Function

MONTH (ODBC 1.0)

Month Function

MONTHNAME (ODBC 2.0)

MonthName Function

NOW (ODBC 1.0)

Now Function

QUARTER (ODBC 1.0)

DatePart Function

SECOND (ODBC 1.0)

Second Function

TIMESTAMPADD (ODBC 2.0)

DateAdd Function

TIMESTAMPDIFF (ODBC 2.0)

DateDiff Function

WEEK (ODBC 1.0)

DatePart Function

YEAR (ODBC 1.0)

Year Function

A subset of the conversion functions are supported. The following table lists the ODBC Scalar functions and the equivalent Access functions if any.

ODBC Function

Access Function

CONVERT

Type Conversion Functions

Note    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. To convert a string to currency, use SQL_FLOAT. For more information, see Explicit Data Type Conversion Function.

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.