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 |