[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 349
  • Last Modified:

Run-time error '3612'

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
kirthir
Asked:
kirthir
  • 6
  • 6
1 Solution
 
paaskyCommented:
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
 
kirthirAuthor Commented:
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
 
nico5038Commented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
kirthirAuthor Commented:
Yeah,

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

kirthir
0
 
paaskyCommented:
so what happened?
0
 
kirthirAuthor Commented:
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
 
paaskyCommented:
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
 
kirthirAuthor Commented:
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
 
paaskyCommented:
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
 
kirthirAuthor Commented:
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
 
paaskyCommented:
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
 
kirthirAuthor Commented:
Even the above way of doing is also taking lot of time..

0
 
paaskyCommented:
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now