Odbc scalar functions

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

Text Math Date/Time Conversion



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.