Help on Sorting a Report using VBA

[ARC]

I have a ranking report where I want to sort it different ways depending on
the option the user picks. On the On_Open event, I've tried everything I can
think of and keep getting error 3071 "This expression is typed incorrectly
or is too complex..."

Here's the 2 things I've tried, both return the error 3071. If I comment out
the code, the report opens normally. The fields SumOfInvTot, Margin,
MarginPCT, and Comp all exist both in the report and in the query.
OrderBy:
----------
Select Case Forms!fReports. Form!lstReports (this is the reportID in a table
of reports)
Case 56 'sales ranking by total invoices $
Me.OrderBy = "SumOfInvTo t DESC, Margin DESC, MarginPct DESC, Comp"
Case 110 'sales ranking by profit $
Me.OrderBy = "Margin DESC, MarginPct DESC, SumOfInvTot DESC, Comp"
end select

(Note: OrderByOnLoad is set to true)

Group Level approach:
------------------------
I added 4 groups to the report, and set all 4 to "without a header section"
and "without a footer section", so really there are 4 sort levels.

Select Case Forms!fReports. Form!lstReports
Case 56 'sales ranking by total invoices $
Me.GroupLevel(0 ).ControlSource = "SumOfInvTo t"
Me.GroupLevel(0 ).SortOrder = True 'Set to Descending
Me.GroupLevel(1 ).ControlSource = "Margin"
Me.GroupLevel(1 ).SortOrder = True 'Set to Decending
Me.GroupLevel(2 ).ControlSource = "MarginPct"
Me.GroupLevel(2 ).SortOrder = True 'Set to Decending
Me.GroupLevel(3 ).ControlSource = "Comp"
Me.GroupLevel(3 ).SortOrder = False 'Set to Acending
Case 110 'sales ranking by profit $
Me.GroupLevel(0 ).ControlSource = "Margin"
Me.GroupLevel(0 ).SortOrder = True 'Set to Decending
Me.GroupLevel(1 ).ControlSource = "MarginPct"
Me.GroupLevel(1 ).SortOrder = True 'Set to Decending
Me.GroupLevel(2 ).ControlSource = "SumOfInvTo t"
Me.GroupLevel(2 ).SortOrder = True 'Set to Decending
Me.GroupLevel(3 ).ControlSource = "Comp"
Me.GroupLevel(3 ).SortOrder = False 'Set to Accending
end select
Any ideas? This one's driving me batty....

[Salad]

ARC wrote:

I have a ranking report where I want to sort it different ways depending
on the option the user picks. On the On_Open event, I've tried
everything I can think of and keep getting error 3071 "This expression
is typed incorrectly or is too complex..."

Here's the 2 things I've tried, both return the error 3071. If I comment
out the code, the report opens normally. The fields SumOfInvTot, Margin,
MarginPCT, and Comp all exist both in the report and in the query.
OrderBy:
----------
Select Case Forms!fReports. Form!lstReports (this is the reportID in a
table of reports)
Case 56 'sales ranking by total invoices $
Me.OrderBy = "SumOfInvTo t DESC, Margin DESC, MarginPct DESC, Comp"
Case 110 'sales ranking by profit $
Me.OrderBy = "Margin DESC, MarginPct DESC, SumOfInvTot DESC, Comp"
end select

(Note: OrderByOnLoad is set to true)

That must be a 2007 thang. Still, I'd put an
Me.OrderByOn = True
after setting the OrderBy value.

>
Group Level approach:
------------------------
I added 4 groups to the report, and set all 4 to "without a header
section" and "without a footer section", so really there are 4 sort levels.

Select Case Forms!fReports. Form!lstReports
Case 56 'sales ranking by total invoices $
Me.GroupLevel(0 ).ControlSource = "SumOfInvTo t"
Me.GroupLevel(0 ).SortOrder = True 'Set to Descending
Me.GroupLevel(1 ).ControlSource = "Margin"
Me.GroupLevel(1 ).SortOrder = True 'Set to Decending
Me.GroupLevel(2 ).ControlSource = "MarginPct"
Me.GroupLevel(2 ).SortOrder = True 'Set to Decending
Me.GroupLevel(3 ).ControlSource = "Comp"
Me.GroupLevel(3 ).SortOrder = False 'Set to Acending
Case 110 'sales ranking by profit $
Me.GroupLevel(0 ).ControlSource = "Margin"
Me.GroupLevel(0 ).SortOrder = True 'Set to Decending
Me.GroupLevel(1 ).ControlSource = "MarginPct"
Me.GroupLevel(1 ).SortOrder = True 'Set to Decending
Me.GroupLevel(2 ).ControlSource = "SumOfInvTo t"
Me.GroupLevel(2 ).SortOrder = True 'Set to Decending
Me.GroupLevel(3 ).ControlSource = "Comp"
Me.GroupLevel(3 ).SortOrder = False 'Set to Accending
end select
Any ideas? This one's driving me batty....

If you step thru the code does it break on the
Me.GroupLevel(0 ).ControlSource =...
or the
Me.GroupLevel(0 ).SortOrder = ...
or some other line? Maybe put the word
STOP
just before the
Select Case Forms!fReports. Form!lstReports
line. It might help if we knew where it was blowing up.

[ARC]

Ok, now I feel silly... I was only taking a quick look at the query results,
and there were many records. Well, if I scrolled to the bottom, I had a
#Error in the Margin % field, so the one bad query result was causing the
error 3071...
"ARC" <PC*****@PCESof t.invalidwrote in message
news:1g******** ********@nlpi07 0.nbdc.sbc.com. ..

>I have a ranking report where I want to sort it different ways depending on
the option the user picks. On the On_Open event, I've tried everything I
can think of and keep getting error 3071 "This expression is typed
incorrectly or is too complex..."

Here's the 2 things I've tried, both return the error 3071. If I comment
out the code, the report opens normally. The fields SumOfInvTot, Margin,
MarginPCT, and Comp all exist both in the report and in the query.
OrderBy:
----------
Select Case Forms!fReports. Form!lstReports (this is the reportID in a
table of reports)
Case 56 'sales ranking by total invoices $
Me.OrderBy = "SumOfInvTo t DESC, Margin DESC, MarginPct DESC, Comp"
Case 110 'sales ranking by profit $
Me.OrderBy = "Margin DESC, MarginPct DESC, SumOfInvTot DESC, Comp"
end select

(Note: OrderByOnLoad is set to true)

Group Level approach:
------------------------
I added 4 groups to the report, and set all 4 to "without a header
section" and "without a footer section", so really there are 4 sort
levels.

Select Case Forms!fReports. Form!lstReports
Case 56 'sales ranking by total invoices $
Me.GroupLevel(0 ).ControlSource = "SumOfInvTo t"
Me.GroupLevel(0 ).SortOrder = True 'Set to Descending
Me.GroupLevel(1 ).ControlSource = "Margin"
Me.GroupLevel(1 ).SortOrder = True 'Set to Decending
Me.GroupLevel(2 ).ControlSource = "MarginPct"
Me.GroupLevel(2 ).SortOrder = True 'Set to Decending
Me.GroupLevel(3 ).ControlSource = "Comp"
Me.GroupLevel(3 ).SortOrder = False 'Set to Acending
Case 110 'sales ranking by profit $
Me.GroupLevel(0 ).ControlSource = "Margin"
Me.GroupLevel(0 ).SortOrder = True 'Set to Decending
Me.GroupLevel(1 ).ControlSource = "MarginPct"
Me.GroupLevel(1 ).SortOrder = True 'Set to Decending
Me.GroupLevel(2 ).ControlSource = "SumOfInvTo t"
Me.GroupLevel(2 ).SortOrder = True 'Set to Decending
Me.GroupLevel(3 ).ControlSource = "Comp"
Me.GroupLevel(3 ).SortOrder = False 'Set to Accending
end select
Any ideas? This one's driving me batty....