Solved

Run-time error '3612'

Posted on 2000-03-03
13
315 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

740 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