ODBC Scalar Functions

Access for Microsoft 365, Access 2019, Access 2016, Access 2013, Access 2010, Access 2007.

Microsoft Access SQL advocates the use of the ODBC specified syntax for scalar functions in a pass-through query that functions on Microsoft SQL Server. For example, to garner all rows where the absolute value of the volatility in the stock price was greater than five, apply 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

TextMathDate/TimeConversion

A subset of the text functions are managed. The following table states the ODBC Scalar functions and the corresponding 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

Leave a Reply

%d bloggers like this: