select order by syntax

[Kevin Payne]

Having problems with simple syntax in access. This is in an ASP page calling
an Access 2000 table. Following select:

"SELECT APPL_ABBR,APPL_NAM, " +_
"UCASE(TRIM(APPL_ABBR)) AS [UPABBR], " +_
"UCASE(TRIM(APPL_NAM)) AS [UPNAME] " +_
"FROM Application " +_
"ORDER BY UPABBR ASC"

I believe it is the order by clause that I don't quite have the syntax right
with the selecting fields "AS".

Help appreciated!!!
KP

 

[Douglas J. Steele]

Try removing the square brackets from around the field names after the AS
clauses.

However, I think your problem may be the use of the UCASE and TRIM
functions. They're VBA functions, and I don't believe you can use them from
ASP.

Try using

"SELECT APPL_ABBR,APPL_NAM " +_
"FROM Application " +_
"ORDER BY UPABBR ASC"

and then apply the UCase and Trim functions in your script.

 

[Van T. Dinh]

There are 2 problems here:

1. You cannot use an Alias in the Order By clause. If you need to do so,
you need to repeat the whole expression like:

... ORDER BY UCASE(TRIM(APPL_ABBR))

2. Since you access JET data from outside Access, the VBA functions such as
Trim() and UCase() may not be available to you.

 

[Kevin]

#1 was it. In Visual Foxpro, you can use the alias in the order by clause.
Thanks!!!

There are 2 problems here:

1. You cannot use an Alias in the Order By clause. If you need to do so,
you need to repeat the whole expression like:

... ORDER BY UCASE(TRIM(APPL_ABBR))

2. Since you access JET data from outside Access, the VBA functions such as
Trim() and UCase() may not be available to you.

--
HTH
Van T. Dinh
MVP (Access)

Having problems with simple syntax in access. This is in an ASP page calling
an Access 2000 table. Following select:

"SELECT APPL_ABBR,APPL_NAM, " +_
"UCASE(TRIM(APPL_ABBR)) AS [UPABBR], " +_
"UCASE(TRIM(APPL_NAM)) AS [UPNAME] " +_
"FROM Application " +_
"ORDER BY UPABBR ASC"

I believe it is the order by clause that I don't quite have the syntax right
with the selecting fields "AS".

Help appreciated!!!
KP