ORDER BY ... If (SQL syntax question)

[Remster]

Hi Folks



I have the following as the Row Source for a list box:

SELECT Field1, Field2 ORDER BY Field1, Field2;

Supposing the possible values for Field1 are 1 and 2, how do I get Field2 to sort in ascending order if the value in Field1 is 1 but in descending order if the value in Field1 is 2? I'm guessing it's something like this, but I don't know enough about SQL to work out the correct syntax:

SELECT Field1, Field2 ORDER BY Field1, If(Field1=1, Field2 ASC, Field2 DESC);

Any help would be very welcome.

Thanks

Remster

[khalid]

I don't think it would work but try this:

SELECT Field1, Field2 ORDER BY Field1, IIf(Field1=1, Field2 ASC, Field2 DESC);

IF is not the correct syntax in you select statement.

[Remster]

Alas, I get an error message (the same error message) telling me there's a missing operator in the expression.

[Rixxe]

Missing a FROM?

SELECT Field1, Field2 ORDER BY Field1, IIf(Field1=1, Field2 ASC, Field2 DESC) FROM Table1;

[Remster]

No, I have the 'FROM' there. I just left it out of my example for simplicity.

[Remster]

I've tried a few more things and the problem seems to lie in including 'ASC' and 'DESC' in the 'IIf' statement. The following, for example, works fine:

SELECT Field1, Field2, Field3 ORDER BY Field1, IIf(Field1=1, Field2, Field3);

So how do I smuggle in 'ASC' and 'DESC' without generating a syntax error?

[khalid]

Hi,
You are right you can not use ASC, DEC in the form of IIf(Field1=1, Field2 ASC, Field2 DESC) which would definitely produce a syntax error.
However what about this:

SELECT Field1, Field2 ORDER BY Field1, IIf(Field1=<Field2, Field1, Field2);

[Remster]

Hmm, that doesn't work, but I'm not sure why it would. What I'm trying to achieve is this:

• First all records are sorted by Field1.
• Then all records where Field1=1 are sorted by Field2 in descending order.
• And all records where Not(Field1=1) are sorted by Field2 in ascending order.

For example:

Field1_________Field2
1______________Z
1______________Y
1______________X
1______________W
1______________V
2______________V
2______________W
2______________X
2______________Y
2______________Z
3______________V
3______________W
3______________X
3______________Y
3______________Z

[Remster]

Got it at last, mostly through trial and error:

SELECT Field1, Field2 ORDER BY Field1, IIf(Field1=1, TableName.Field2, "") DESC, IIf(Not(Field1=1), TableName.Field2, "") ASC;

[khalid]

Great! that you got through