Solved

Run-time error '3612'

Posted on 2000-03-03
13
314 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

827 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