Trouble with query for report

[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

 

[Duane Hookom]

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.

 

[Michael A. Bennett]

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

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

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

 

[Duane Hookom]

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

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

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

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