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.... 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.
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....
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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,...
|
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...
|
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
|
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 *** ...
|
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,...
|
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?
|
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...
|
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...
|
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...
|
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"...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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
|
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...
| |