By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,995 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

Help on Sorting a Report using VBA

P: n/a
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 = "SumOfInvTot 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 = "SumOfInvTot"
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 = "SumOfInvTot"
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....

Oct 28 '08 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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 = "SumOfInvTot 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 = "SumOfInvTot"
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 = "SumOfInvTot"
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.

Oct 28 '08 #2

P: n/a
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*****@PCESoft.invalidwrote in message
news:1g****************@nlpi070.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 = "SumOfInvTot 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 = "SumOfInvTot"
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 = "SumOfInvTot"
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....
Oct 28 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.