Return
-
Title
Error given when trying to run a query that collects a substring in Access. "Undefined function ' -
Description
When trying to run a query that collects a substring, an error is given.
Example 1:
SELECT substring('my name is Aaron Smith', 12, 5) as test_sub from ADDRESS
"Reserved error (-1001); there is no message for this error"
Example 2:
SELECT substr('my name is Aaron Smith', 12, 5) as test_sub from ADDRESS
"Undefined function 'substr' in expression"
-
Cause
SUBSTRING/SUBSTR is not a defined function in Access.
-
Resolution
Use the function MID() for substring functionality.
Example:
SELECT mid('my name is Aaron Smith', 12, 5) as test_sub from ADDRESS
-
Additional Information
Reference for Access string functions: