Incompatible data types in case statement - Dynamic sort prompt

[rRc54]

I've read the other posts pertaining to this issue and they've been invaluable in working through the process of resolving this to get to this point.  Unfortunately, they ultimately have not proven fruitful.  Some background:

I'm putting together a farily straightforward report that presents the users with the ability to sort columns with prompts.  One prompt selects the column and the other selects Ascending/Descending.  I initially ran into problems with incompatible data types because I was trying to include all the desired columns in one query calc; One column is text and the rest are numbers (dollar amounts).  I split out the text and numbers into different query calcs, and tweaked and massaged.  The text calc is working perfectly but the number ones are still generating incompatible data type errors

A sample of the number query calc logic is below:

Case When ?p_SortType? = 'Ascending'
Then
Case ?p_SortColumn?
When 2 Then [Balance Due]
Else 0
End
Else
''
End

I've tried a variety of solution attempts but I'm still stuck.

Thanks in advance for any assistance.

[dougp]

I read your query calc logic as follows:
If SortType = Ascending and SortColumn = 2, return a decimal value.
If SortType = Ascending and SortColumn <> 2, return an integer.
If SortType <> Ascending, return a string.

What is the source of your surprise?

[rRc54]

dougp:  Thanks a bunch for this.  It's proven very helpful.  (A classic example of not seeing the forest for the trees)...

R