473,320 Members | 1,872 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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 = "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
2 5403
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Steve Patrick | last post by:
Hi All You guys are my last hope, despite spending money on books and hours reading them I still can not achieve the results I need. I have designed a database in Access 2000 based on 1 table,...
8
by: Mike MacSween | last post by:
tblCourses one to many to tblEvents. A course may have an intro workshop (a type of event), a mid course workshop, a final exam. Or any combination. Or something different in the future. At...
3
by: Paul T. Rong | last post by:
Hello there, I am making a report using codes bellow (a part of): ------------------------------- Select Case Forms!!TopBoard Dim strGetSQL As String
1
by: Jon via AccessMonster.com | last post by:
Hi Guys, My "Sorting and Grouping" in my report looks like this: Field/Expression Sort Order sID Ascending ((= Project Name Ascending User *** ...
5
by: Mike | last post by:
Hello All, I have a report based upon a query. I have added a control to report footer that calcs the total cost of the inventory: =SUM(). When this total calculation is NOT on the report,...
3
by: Jimmy | last post by:
Is there a way to sort/group a report based on the second column of a combo box, i.e. the text associated with the primary key number?
3
by: Don | last post by:
I have a "Report" that is created from a "Form". It prints a list of items, you may consider it a shopping list. In any event I use to run this in alphabetical order but have since decided to run...
4
by: access baby | last post by:
i have a huge database based on date and time need to create different report we need to measure our work processes how many order received , order cancelled, completed and count of items completed...
1
by: access baby | last post by:
Hi Below mention is the reply from Salad on my query i created a crosstab query and form not based on any table of qurey but this doesnt work . I somehow have missed something actually i have too...
8
by: sara | last post by:
Hi - I have looked at all posts and tried both Allen Browne's Report Sorting at run Time ( Select Case Forms!frmChooseSort!grpSort Case 1 'Name Me.GroupLevel(0).ControlSource = "LastName"...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.