ORDER BY Sorting Integer Field as Text

[Kluaoha]

I have a query selecting many fields, and based on a boolean value I want it to be sorted along one of two fields:

...
ORDER BY IIF(GetBooleanValue(),[MyTable].[MyField1],[MyTable].[MyField2]);

GetBooleanValue is a function in Globals module, MyField1 is a long integer field, MyField2 is a short text field.
It works just fine, except when GetBooleanValue() is true, it sorts MyField1 as if it were text.

[pbaldy]

My first thought is to do the sorting in the form/report the query is used for.

[June7]

I tested this and doesn't matter if True or False, only sorts on Field1, never Field2 and alpha sort is applied to Field1.

However, if both fields are number type then number sort is applied and then the True/False condition works and Field2 will return.

So my conclusion is IIf returns a string if either field is text, so both fields must be number type if you want number sort.

[Kluaoha]

Thanks, I got it to work by just changing the RowSource property when the Boolean changed.