string to sort as numeric (1 Viewer)

[g-richardson]

Here is another version of the old song.

I have a field which considers two logical fields and if either is true, return a string phrase, if neither is true, return a value.

ReportPremium: Format(IIF([LogicA] = True, "LogicA", IIF([LogicB] = True, "LogicB",[Premium])),"$#,##0")

This all gets sorted in ascending order with the LogicA and LogicB getting to the bottom of the list.


When the results come back everything works fine until I get a value of 13,000 being ranked as < 9,000...and so sorts above 9,000.

I tried removing the format instruction above with cInt() and got a response that the query was too complex. When I replaced the format instruction with Val() it generated the same results, but stripped the format instruction.

ReportPremium: Val(IIF([LogicA] = True, "LogicA", IIF([LogicB] = True, "LogicB",[Premium])))


I need to get the [premium] data to sort as numerics, but force the "LogicA" and "LogicB" to go to the bottom of the list when a result is true.

example:
7,000
9,000
13,000
LogicB


Can anyone help?

Thanks,

 

[pbaldy]

First thought that comes to mind is another calculated field that returns 0 for numeric entries and 1 for text entries, and sort on that first.

 

[g-richardson]

Awesome...that did it...sooooo simple.

Thanks so much.

George