Sort Order Problem

[mikelev]

I have a query that works fine except the order by clause.

The following order by clause I get:

ORDER BY VAL([TABLES Query].TABLENUM);

P5
P4
P3
P2
P1
1
2
3
4
5
6
7
8
9
10


I would like the P* values at the bottom:

1
2
3
4
5
6
7
8
9
10
P1
P2
P3
P4
P5

Is this possible?

Cheers,

 

[smedvid]

Not sure u can do it easily in a single Order by. Perhaps consider adding a constructed field, where as it is dervied from the field. For example, IIF(Mid(FieldName,1,1)="P", "A","B"). Then sort on that field, along with the current field.

Select IIF(Mid(FieldName,1,1)="P", "A","B") as SortField, FieldName
from xTable
order by 1,2


htwh,

Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[PHV]

ORDER BY IIf([TABLES Query].TABLENUM Like '#*', VAL([TABLES Query].TABLENUM),[TABLES Query].TABLENUM);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mikelev]

Thanks for the responses. I was able to resolve the issue by adding a column "Sort" to the TABLES table and applying a numeric value to get them sorted the way I wanted.