I'm building a report and I'm trying to get the values of a specific column to sort ascending (ignoring the +/- in front) IE.. 10, 15, 100, -100, 1000, 1500, -1500 instead of -1500, -100, 10, 15, 100, 1000, 1500. I've applied the Advance filter to the Query, it runs in the order I'm looking for but when I put it into a report and start to manipulate the Header info , it reverts back to the Negative numbers at the top.
My Advance Query filter is as follows:
Expr1: IIf(Left([MyNumber],1)="-",Mid([MyNumber],2),[MyNumber]). I've replaced MyNumber with the actual field name and as i said the Query returns the sorts in the correct order for my needs.
What am I missing.