Change order in query

[davidmcolaco]

Hi,

I have a query to get some data from a table and order it, but I need to order not in ascending way. I have to order it by a fiels that has 3 values: "Sup. A", "Sup. B" and "Sup. C", but in the final I want to appeared order like this:
"Sup. A"
"Sup. C"
"Sup. B"

How can I do this?

Thanks.

 

[jebry]

Hi!

Add a field to the query but don't display it:

SortField: IIf([YourField] = "Sup. A", 1, IIf([YourField] = "Sup. C", 2, 3))

Then sort by this field.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[davidmcolaco]

Hi,

I tried that expression on criteria, but I've got the following error: The expression you entered has an invalid . (dot) or ! operator or invalid parentheses.
This is my final expression:
Sortfield: If([Supervisão]="Sup. Congresso", 1, If([Supervisão]="Sup. Programa", 2, 3))

Can you understand what's the problem with the expression?

Thanks.

 

[Golom]

Yes

Sortfield: [COLOR=red yellow]I[/color]If([Supervisão]="Sup. Congresso", 1, [COLOR=red yellow]I[/color]If([Supervisão]="Sup. Programa", 2, 3))

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[davidmcolaco]

No, that was an error of the writing, but with:
Sortfield: If([Supervisão]="Sup. Congresso", 1, If([Supervisão]="Sup. Programa", 2, 3))

I got that error I told before.

Thanks in advance.

 

[Golom]

You are still using IF rather than IIF.

IF is a VBA programming construct that is valid only in VBA code ... not in SQL.

IIF is an immediate or in-line IF that is valid in both environments.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[davidmcolaco]

So from what I understand I cannot use if and iif.
So how can do it with the if or iff.

Thanks.

 

[Golom]

You have posted twice saying that you were getting errors with the code

Sortfield: If([Supervisão]="Sup. Congresso", 1, If([Supervisão]="Sup. Programa", 2, 3))

On three occasions (jebry and me) it has been suggested that you use IIF in place of IF.

Now ... Have you done that?

To re-emphasize ... IF will not work in an SQL statement but IIF will.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]