Using SUBSTRING in query

[walter via AccessMonster.com]

This is a query-of-query in ColdFusion and MSAccess

This works ok

<cfquery name="session.getAlpha" dbtype="query">
select distinct signerName
from session.getSigs
</cfquery>

Instead, I would like to select just the "first letter" of signerName in
alphabetical order:

<cfquery name="session.getAlpha" dbtype="query">
select distinct substring(signerName,1,1) as alpha
from session.getSigs
orderby alpha
</cfquery>

and it fails. This is giving me a rough time, im just not making the query
right ...
Any help much appreciated!

 

[Duane Hookom]

You are attempting to run a query of queries that has a more limited support
for SQL and functions. You might need to create the alpha column in your
sessions.getSigs. You could then group by the alpha in the query of queries.

 

[John Vinson]

Instead, I would like to select just the "first letter" of signerName in
alphabetical order:

<cfquery name="session.getAlpha" dbtype="query">
select distinct substring(signerName,1,1) as alpha
from session.getSigs
orderby alpha
</cfquery>

and it fails. This is giving me a rough time, im just not making the query
right ...
Any help much appreciated!

If Access is processing the query (I don't use ColdFusion so I'm not
sure how dbtype="query" will work), use the Access function Left()
instead of the ColdFusion function substring:

select distinct Left(signername. 1) as alpha

The substringing operation is handled in Access using the functions
Left(), Right(), and Mid(). See the VBA online help for syntax.

John W. Vinson[MVP]