Solved

Run-time error '3612'

Posted on 2000-03-03
13
310 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
  • 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
 
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now