- Fieldname "name" contains a person's name as "John Smith" (firstname lastname).

Need to SELECT this field - among others - but the result should be a transposed name, like "Smith, John" with a comma, and also be able to sort in ascending and descending order.

I am tring (in ColdFusion):

<cfquery name="XXX" datasource="people">
select
substring(tpcname,PATINDEX('% %',tpcname)+1,2000) + ', ' + substring(tpcname,1,PATINDEX('% %',tpcname))
from topics
order by substring(tpcname,PATINDEX('% %',tpcname)+1,2000)
</cfquery>

but I get

ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC Microsoft Access Driver] Undefined function 'substring' in expression.

Thanks for helping