Order By 2 ways on 1 field?

[Zoon]

A text field named SKU has in it strings like these; 1005693, 88800245, ABC-5846703, XYZ-23710. How can I sort or Order a Select query using the first character in SKU unless the fourth character is a dash, then I want to use the fifth character to sort?

The query should return this;
1005693
XYZ-23710
ABC-5846703
88800245

Not this;
1005693
88800245
ABC-5846703
XYZ-23710

 

[cmmrfrds]

substitue your field names.

SELECT SKUtable.id, SKUtable.sku
FROM SKUtable
ORDER BY IIf([sku]>=&quot;0&quot; and sku<=&quot;9&quot;,[sku],Mid([sku],5,Len([sku])-4));

 

[dhookom]

SortOrder:IIf(Mid([SKU],4,1)=&quot;-&quot;,Mid([SKU],5),[SKU])

Duane
MS Access MVP

 

[wtmckown]

The only way I can thik of to accomplish this is to use a union query. It would go something like this:

SELECT Table1.sSKU, Mid([sSku],5,Len([sSku])) AS SortValue
FROM Table1
WHERE (((Mid([sSku],4,1))=&quot;-&quot) UNION SELECT Table1.sSKU, [sSku] AS SortValue
FROM Table1
WHERE (((Mid([sSku],4,1))<>&quot;-&quot) Order By SortValue ;

HTH...Bill Mckown