Solved

Run-time error '3612'

Posted on 2000-03-03
13
325 Views
Last Modified: 2010-05-18
When I try to open a report, it is giving the following error message
"Multi-level Group by clause is not allowed in a subquery"
But i was able to execute the underlying query independantly.

Can anybody throw some light on this?
0
Comment
Question by:kirthir
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
13 Comments
 
LVL 10

Expert Comment

by:paasky
ID: 2580999
Hello kirthir,

I assume you have defined groups into your report and when you run the report Access is parsing your query (which has GROUP BY clause too?) and fails to group SQL to work with Report. Please post your query here and information how your report is grouped.

Regards,
Paasky
0
 
LVL 1

Author Comment

by:kirthir
ID: 2581075
Let me explain you in detail.

The underlying query is a simple select query with some inner join, and the report had one group header (with sorting & grouping option set to one of the fields from the select query).

This particular report worked fine until i changed one of the column to an expression i.e.,

The fieldname "pubno"
got replaced with the following expression  

"iif (pubtype in (select [pub type] from [type list] where trim(description) = '1'), PUBTYPE+' '+PUBNO+REV , pubno) AS pubnumber"

Waiting to hear from u
0
 
LVL 54

Expert Comment

by:nico5038
ID: 2582783
Checked that Pubno isn't available anymore for your report but that you have to use pubnumber instead ?!
(Report field and grouping options must be changed !)
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 1

Author Comment

by:kirthir
ID: 2587411
Yeah,

I did replace "pubno" with "pubnumber"
in my report also.

kirthir
0
 
LVL 10

Expert Comment

by:paasky
ID: 2587439
so what happened?
0
 
LVL 1

Author Comment

by:kirthir
ID: 2587461
What i mean to say is i already replaced "pubno" with "pubnumber" in my report with no result i.e., with problem still persisting.

0
 
LVL 10

Expert Comment

by:paasky
ID: 2587539
Perhaps it would help if you change your iff - sub select to DCount aggr. function

Something like this:

"iif (DCount("*","[type list]","trim([description])='1'")>0, PUBTYPE+' '+PUBNO+REV , pubno) AS pubnumber"

Hope this helps,
Paasky
   
0
 
LVL 1

Author Comment

by:kirthir
ID: 2587595
We can get the number of records using DCount
but how can i know whether the field "pubtype" of another table (let us say TableA) exists in the set of records returned by "[type list]" table.


0
 
LVL 10

Accepted Solution

by:
paasky earned 50 total points
ID: 2587686
here's corrected iif..

"iif (DCount("*","[type list]","trim([description])='1' and [pubtype]='" & [pubtype] & "'")>0, PUBTYPE+' '+PUBNO+REV , pubno) AS pubnumber"

I realized that part was missing just after posting, sorry.

Paasky
0
 
LVL 1

Author Comment

by:kirthir
ID: 2587928
Hi paasky,

Thanks for your response,

I was able to get the required effect using the "iif" provided by you but the query is taking lot of time to execute i.e., it is very slow...

If you know any fast way of executing it, let me know....

Currently i was also able to get the required effect in a round-about manner with an additional query(selecting the fields from the first query which contains iif statements )

0
 
LVL 10

Expert Comment

by:paasky
ID: 2587997
Domain aggregate functions are slooooww.. I wonder would it be faster if you add this simple little function into your public modules and use it instead.

Public function Get_PubNumber(Pubtype as string, pubno as variant, rev as variant) as variant
   dim rst as recordset
   dim strSQL as string

   strSQL = "SELECT 'x' FROM [type list] WHERE Trim(Description) = '1' AND [pub type] = '" & pubtype & "'"

   set rst = CurrentDB.Openrecordset(strSQL,dbOpenSnapShot)
   if .BOF and .EOF then
       Get_PubNumber = pubno
   else
       Get_PubNumber = PUBTYPE+" "+PUBNO+REV
   end if
   set rst = nothing
End Function

usage:

PubNo = Get_PubNumber(Pubtype, pubno, rev)

Or in query

SELECT Get_PubNumber([Pubtype], [pubno], [rev]) As PubNumber
FROM TABLE1;

Regards,
Paasky
0
 
LVL 1

Author Comment

by:kirthir
ID: 2588041
Even the above way of doing is also taking lot of time..

0
 
LVL 10

Expert Comment

by:paasky
ID: 2588081
Are you using pubnumber grouping your report? If not, you could format the  field when printed out - not in query...

Also you could add [type list] table in your query and use Outer (left/right) join to make relation between the source table. Then you could format the pubnumber in your report like this:

=Iif(IsNull([some "type list" field]), PubNo, PUBTYPE+" "+PUBNO+REV)

This should be much much efficient.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question