Order query results by field value 1

[handlebarry]

I'm trying to order the results of a query by the values in a particular field.

The field has a criteria limiting it to "NOI INCOMPLETE INFO", "NOI PREPARTION" and "NOI OBSERVATION"

I need the records to be sorted by NOI INCOMPLETE INFO first then NOI PREPARATION then NOI OBSERVATION

is this possible?

 

[HarleyQuinn]

Go into the SQL view and try (after your where clause):

ORDER BY NOI INCOMPLETE INFO,NOI PREPARATION, NOI OBSERVATION

Hope thos helps

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[HarleyQuinn]

If you want to do it in the query builder window then there is a dropdown list called sort for each field you are using. If you click that you can sort the results of your query that way. It will basically do what I said in my first post but you don't have to look at the SQL.

Hope this helps

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[M8KWR]

it is possible but you have to be a bit cunning.

in the query use a mid statement like the code below. But do not show the field, just use an expr1 expression, you are only using this to sort the query...

Mid([field1],7,1)

If you sort is ascending order it will order the value as you want.

I hope this helps...

 

[HarleyQuinn]

I mis-read that one a bit there...

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[PHV]

ORDER BY IIf(yourField="NOI INCOMPLETE INFO", 1, IIf(yourField="NOI PREPARATION", 2, IIf(yourField="NOI OBSERVATION", 3, 9)))

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

 

[handlebarry]

thanks M8KWR, that was indeed very cunning

Unfortunatly, for simplicity, I did not give you the complete criteria list, so I can't use it!!

Thanks for your response PHV, looks good as always, will give it a go now