custom sorting with IIF statement

[orangeman2003]

Hi...I need some help to finish an IIF statement. The weird thing is I can perfectly do what I want to in the design view of my query editor but when I try to do it in the report expression builder can't get it.



I'm trying to do a custom sort where Tier 1 is sorted alphabetically and tier 2 is custom sort. The way it goes is where the name of tier 2 = the name of tier 1, sort this first, then do ascending.

Example:

Mavericks (tier 1)
Mavericks (tier 1)
Clippers (tier 2)
Lakers (tier2)
Wizards (Tier 2)

my sql code is:

ORDER BY tbl_baselinesystems.APPNAME, IIf(tbl_exchanges.OriginNode=tbl_baselinesystems.A PPNAME,0,1), tbl_exchanges.OriginNode;

When I put either IIf(tbl_exchanges.OriginNode=tbl_baselinesystems.A PPNAME,0,1) or IIf(tbl_exchanges.OriginNode=tbl_baselinesystems.A PPNAME,0, IIf(tbl_exchanges.OriginNode=tbl_baselinesystems.A PPNAME,1)) into expression builder neither work. Is there a way for me to sort in an expression using regular sql.

[ranman256]

Build all your sort fields in the query. Not in the report.
And if the IIF statmts get too hairy, make a function. That way you only build it once, and not for every field.

[orangeman2003]

I did the first part, I created the sort in the query and when you run it, it works great. for some reason when I add that query to the report and then I add the associated field it doesn't hold the sort. not too familiar with building a function...

[June7]

Sorting in reports works different. Seems report usually ignores the query sort. Use the report Grouping & Sorting functionality.

[orangeman2003]

that's what I'm doing, I'm using an IIF statement to sort a custom expression.

=IIf([OriginNode]=[APPNAME],0,1)

this brings up a statement of order by clause conflicts with group by. it might just be that i can't sort it this way with an expression

[June7]

I looked again at the data example in OP. Both Mavericks show tier 1, should one be tier 2?

You could be right about the impracticality of this. You want to assign a priority ranking to the Mavericks tier 2 record because there is also a tier 1 Mavericks. This is a criteria that is dependent on value in another record. What if the Lakers also had a tier 1 - how would the output look?