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) |
|
BIT_LENGTH (3.0) |
(No equivalent) |
CHAR ( ODBC 1.0) |
|
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) |
|
LEFT ( ODBC 1.0) |
|
LENGTH ( ODBC 1.0) |
|
LOCATE ( ODBC 1.0) |
|
LTRIM ( ODBC 1.0) |
|
OCTET_LENGTH ( ODBC 3.0) (No equivalent function) |
(No equivalent) |
POSITION ( ODBC 3.0) |
|
REPEAT ( ODBC 1.0) |
|
REPLACE ( ODBC 1.0) |
|
RIGHT ( ODBC 1.0) |
|
RTRIM ( ODBC 1.0) |
|
SOUNDEX ( ODBC 2.0) |
(No equivalent) |
SPACE ( ODBC 2.0) |
|
SUBSTRING ( ODBC 1.0) |
|
UCASE ( ODBC 1.0) |
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) |
|
ACOS (ODBC 1.0) |
(No equivalent) |
ASIN (ODBC 1.0) |
(No equivalent) |
CEILING (ODBC 1.0) |
(No equivalent) |
ATAN (ODBC 1.0) |
|
ATAN2 (ODBC 2.0) |
(No equivalent) |
COS (ODBC 1.0) |
|
COT (ODBC 1.0) |
(No equivalent) |
DEGREES (ODBC 2.0) |
(No equivalent) |
EXP (ODBC 1.0) |
|
FLOOR (ODBC 1.0) |
(No equivalent) |
MOD (ODBC 1.0) |
(Use the MOD operator) |
LOG (ODBC 1.0) |
|
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) |
|
ROUND (ODBC 2.0) |
|
SIGN (ODBC 1.0) |
|
SIN (ODBC 1.0) |
|
SQRT (ODBC 1.0) |
|
TAN (ODBC 1.0) |
|
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) |
|
CURTIME (ODBC 1.0) |
|
CURRENT_DATE (ODBC 3.0) |
|
CURRENT_TIME (ODBC 3.0) |
|
CURRENT_TIMESTAMP (ODBC 3.0) |
|
DAYNAME (ODBC 2.0) |
|
DAYOFMONTH (ODBC 1.0) |
|
DAYOFWEEK (ODBC 1.0) |
|
DAYOFYEAR (ODBC 1.0) |
|
EXTRACT (ODBC 3.0) |
|
HOUR (ODBC 1.0) |
|
MINUTE (ODBC 1.0) |
|
MONTH (ODBC 1.0) |
|
MONTHNAME (ODBC 2.0) |
|
NOW (ODBC 1.0) |
|
QUARTER (ODBC 1.0) |
|
SECOND (ODBC 1.0) |
|
TIMESTAMPADD (ODBC 2.0) |
|
TIMESTAMPDIFF (ODBC 2.0) |
|
WEEK (ODBC 1.0) |
|
YEAR (ODBC 1.0) |
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 |
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.