Trouble with query for report

  • Thread starter Thread starter Mike Bennett
  • Start date Start date
M

Mike Bennett

I'm getting the message "Multi-level group-by clause is
not allowed in a subquery."

The query that the report is based on is very complex. One
field is calculated based on the value of other fields in
the query. If I try to use this calculated field, then I
get this error. If I remove the field from the report,
then the error goes away.

The value of the calculated field is "IIF([Num] = 1,
(SELECT Table1.Field1 FROM Table1 WHERE Table1.Field2 = val
(Table2.Field1) AND Table1.Field3 = Table2.Field3;),
(SELECT Table1.Field1 FROM Table1 WHERE Table1.Field2 = val
(Table2.Field1) AND Table1.Field3 = 'M';)) As CalcField.

Why, when I use CalcField in my report's detail section,
do I get the "Multi-level Group-By clause" error mentioned
above? How can I do this differently?

Thanks!
Mike
 
You may need to replace the subqueries with DLookup().
IIF([Num] = 1,
DLookup("Field1", "Table1", "Field2 = " & val(Table2.Field1) & " AND Field3
= '" & Table2.Field3 & "'"),
DLookup("Field1", "Table1", "Field2 = " & val(Table2.Field1) & " AND Field3
= 'M'")) As CalcField

Or

DLookUp("Field1", "Table1", "Field2 = " & val(Table2.Field1) & " AND Field3
= " & IIf([Num]=1, "'" & Table2.Field3 & "'", "'M'") )
I'm not sure on the quotes and single quotes in this.
 
Thanks, Duane.

I kept reading other posts after I posted and found the DLookup function. It
works fine.

I still don't understand why I'm having trouble putting a calculated field
from my query into a report. I've got two other values that I'm trying to
add to my report, with no luck.

Any insight into this?

Thanks again!
Mike

Duane Hookom said:
You may need to replace the subqueries with DLookup().
IIF([Num] = 1,
DLookup("Field1", "Table1", "Field2 = " & val(Table2.Field1) & " AND Field3
= '" & Table2.Field3 & "'"),
DLookup("Field1", "Table1", "Field2 = " & val(Table2.Field1) & " AND Field3
= 'M'")) As CalcField

Or

DLookUp("Field1", "Table1", "Field2 = " & val(Table2.Field1) & " AND Field3
= " & IIf([Num]=1, "'" & Table2.Field3 & "'", "'M'") )
I'm not sure on the quotes and single quotes in this.



--
Duane Hookom
MS Access MVP


Mike Bennett said:
I'm getting the message "Multi-level group-by clause is
not allowed in a subquery."

The query that the report is based on is very complex. One
field is calculated based on the value of other fields in
the query. If I try to use this calculated field, then I
get this error. If I remove the field from the report,
then the error goes away.

The value of the calculated field is "IIF([Num] = 1,
(SELECT Table1.Field1 FROM Table1 WHERE Table1.Field2 = val
(Table2.Field1) AND Table1.Field3 = Table2.Field3;),
(SELECT Table1.Field1 FROM Table1 WHERE Table1.Field2 = val
(Table2.Field1) AND Table1.Field3 = 'M';)) As CalcField.

Why, when I use CalcField in my report's detail section,
do I get the "Multi-level Group-By clause" error mentioned
above? How can I do this differently?

Thanks!
Mike
Click to expand...
Click to expand...
 
This error generally occurs when attempting to sort by the field that
contains a subquery. DLookup() is the only way I know of to get around it.

--
Duane Hookom
MS Access MVP


Michael A. Bennett said:
Thanks, Duane.

I kept reading other posts after I posted and found the DLookup function. It
works fine.

I still don't understand why I'm having trouble putting a calculated field
from my query into a report. I've got two other values that I'm trying to
add to my report, with no luck.

Any insight into this?

Thanks again!
Mike

Duane Hookom said:
You may need to replace the subqueries with DLookup().
IIF([Num] = 1,
DLookup("Field1", "Table1", "Field2 = " & val(Table2.Field1) & " AND Field3
= '" & Table2.Field3 & "'"),
DLookup("Field1", "Table1", "Field2 = " & val(Table2.Field1) & " AND Field3
= 'M'")) As CalcField

Or

DLookUp("Field1", "Table1", "Field2 = " & val(Table2.Field1) & " AND Field3
= " & IIf([Num]=1, "'" & Table2.Field3 & "'", "'M'") )
I'm not sure on the quotes and single quotes in this.



--
Duane Hookom
MS Access MVP


Mike Bennett said:
I'm getting the message "Multi-level group-by clause is
not allowed in a subquery."

The query that the report is based on is very complex. One
field is calculated based on the value of other fields in
the query. If I try to use this calculated field, then I
get this error. If I remove the field from the report,
then the error goes away.

The value of the calculated field is "IIF([Num] = 1,
(SELECT Table1.Field1 FROM Table1 WHERE Table1.Field2 = val
(Table2.Field1) AND Table1.Field3 = Table2.Field3;),
(SELECT Table1.Field1 FROM Table1 WHERE Table1.Field2 = val
(Table2.Field1) AND Table1.Field3 = 'M';)) As CalcField.

Why, when I use CalcField in my report's detail section,
do I get the "Multi-level Group-By clause" error mentioned
above? How can I do this differently?

Thanks!
Mike
Click to expand...
Click to expand...
Click to expand...
 
Back
Top