asked on
Subquery not working in report
Experts,
I have a subquery that works fine in the query (qryNA3), but when I run a report (qryNA3 as record source) I get the error below. When I remove the subquery, the report works fine. Thank you very much. James
I have a subquery that works fine in the query (qryNA3), but when I run a report (qryNA3 as record source) I get the error below. When I remove the subquery, the report works fine. Thank you very much. James
MRAA: IIf(IsNull((select max(ADate) from tblNAPatient where tblNAPatient.AType="Annual Assessment" and tblNAPatient.PID=qryNA2.PID)),(select max(ADate) from tblNAPatient where tblNAPatient.AType="Initial Assessment" and tblNAPatient.PID=qryNA2.PID),(select max(ADate) from tblNAPatient where tblNAPatient.AType="Annual Assessment" and tblNAPatient.PID=qryNA2.PID))
Microsoft AccessSQL
Last Comment
jdallain
ASKER
Just a second.
ASKER
Sorry, it's a different record source. But it has qryNA3 (the main query) in it.
ASKER
I guess I need to run the subquery from the final query?
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
I had to changed the qryNA2 to qryNA3, but it didn't work. Just got the same error with qryNA3.
ASKER
The qryNA3.PID is there. It works when I put it in qryNA3 and switch it back to qryNA2.PID.
ASKER
I have 3 queries linked to a different ID number (AID) in the report's query. When I removed them, it worked.
ASKER
They are all LEFT JOINED.
Are you SURE that qryNA2.PID is a displayed field in the Record Source ?
mx
mx
ASKER
It didn't work when I switched to INNER JOIN either.
ASKER
Yeah. I think the problem revolves around the links to AID, which is a different ID#.
ASKER
I had the switch to qryNA3.PID
final.JPG
final.JPG
ASKER
It works when I remove queries attached to AID in qryNA3
So, ... Problem solved?
ASKER
Thanks mx! I'll run some seperate querywith the subquery
"Grade: A - "Thanks mx! I'll run some seperate querywith the subquery" "
ooook.
thx.mx
ooook.
thx.mx
ASKER
I didn't have to have that subquery in that query, but it would have been easier. It works for now. I'm sure I'll have to post a follow-up question in a few weeks though. Thanks again mx. James
If you go into report design, click in the Record Source, open the SQL and run it ... what happens ?
mx