does Access have a FIELD() or identical SQL function

[ronverdonk]

Does Access have a FIELD() function or something alike. I want to sort my query result in a non-standard order by specifying e.g.

SELECT * from table_name ORDER BY FIELD(col_name, 'You', 'Me', 'We', 'They')

I use this frequently in MySQL, but I could not find a similar function in MS Access documentation (but I am short sighted and could have overlooked it :-)

Thanks in advance,

Ronald

[ADezii]

Does Access have a FIELD() function or something alike. I want to sort my query result in a non-standard order by specifying e.g.

SELECT * from table_name ORDER BY FIELD(col_name, 'You', 'Me', 'We', 'They')

I use this frequently in MySQL, but I could not find a similar function in MS Access documentation (but I am short sighted and could have overlooked it :-)

Thanks in advance,

Ronald

To the best of my knowledge, there is no equivalent functionality in Access but I'm sure you can improvise. I'm not familiar with the Field() Function in MySQL, so bear with me on this one.

1. Are You, Me, We, and They the only possible values contained within [col_name]?
2. I'm assuming you want the vales in [col_name] sorted by 'You', 'Me', 'We', and 'They'. Is this correct? If so, the solution would be simple enough.
3. If I'm off track, I apologize, please fill me in.

[ronverdonk]

To the best of my knowledge, there is no equivalent functionality in Access but I'm sure you can improvise. I'm not familiar with the Field() Function in MySQL, so bear with me on this one.

1. Are You, Me, We, and They the only possible values contained within [col_name]?
2. I'm assuming you want the vales in [col_name] sorted by 'You', 'Me', 'We', and 'They'. Is this correct? If so, the solution would be simple enough.
3. If I'm off track, I apologize, please fill me in.

I am definitely sure I can improvise, but it would have been nice to let SQL engine do the work for me.

The values specified in the FIELD list will always be first in the result set. Any column values that do not comply with the speficied values will be sorted in the standard way, but shown in the result set after the specified values. So the result would be (simplified):

You
Me
We
They 
All of us
He
She
Whoever

Ronald

[ADezii]

I am definitely sure I can improvise, but it would have been nice to let SQL engine do the work for me.

The values specified in the FIELD list will always be first in the result set. Any column values that do not comply with the speficied values will be sorted in the standard way, but shown in the result set after the specified values. So the result would be (simplified):

You
Me
We
They 
All of us
He
She
Whoever

Ronald

Gotcha. My 'improvised' solution would have been:

1. Create a [Priority] {INTEGER} Field in your Table.
2. Dynamically Update this Field to the following values:
   1. If [col_name] = 'You', set [Priority] = 1.
   2. If [col_name] = 'Me', set [Priority] = 2.
   3. If [col_name] = 'We', set [Priority] = 3.
   4. If [col_name] = 'They', set [Priority] = 4
   5. Remaining values in [Priority] would be assigned Priority Levels (>4) based on their natural Sort Order (code not shown).
   6. Create a Query with the Primary Sort Order of [Priority]/Ascending.
3. I don't think that this can be accomplished by pure SQL, but NeoPa would be the one to see for that. I'll call him in on this.
4. Sorry I couldn't be more helpful.

[ronverdonk]

No sweat. I just wanted to know if it was possible in Access.
Now I do the same (array) sort in my PHP result set, along the lines you showed.

Ronald

[NeoPa]

I've never come across such functionality I'm afraid Ron. I don't believe it exists as standard in Access Jet SQL :(

Access can't handle this easily at all I'm afraid. My personal preference, if faced with such an issue, would be to design a public function procedure in VBA which would evaluate the parameter and return a string value that would sort normally.

I couldn't get the Switch() function to work sensibly as there is no finite list.