Using ORDER BY DECODE in Access

[monika102001]

Is anybody knows if Access has something like Oracle using in SQL 'Order By Decode..'.
What I need to do is order records the way I want not ASC or DESC.I want first with the value 1 then 3 then 2.

Thanks

 

[JoeAtWork]

I've never heard of such a thing in Access. I think if I had that requirement I would create a seperate Decodes table, with one field for the Decode values, the other with the SortOrder number. You could then join the original table to the Decodes table (by the Decode field), and sort by the Decode table's SortOrder field.

 

[mp9]

Or add a calculated field to your query along the lines of:

Ordinal: [FieldName] Mod 2

Then sort by the calculated field ascending first, then the field itself ascending... this approach will put the numbers 1, 2 and 3 into the order 1, 3, 2.

http://www22.brinkster.com/accessory/