Re: [MSAccessProfessionals] Subreport not returning rows

Hi Art,

Can we assume you are not closing the frmWorkOrderlog anywhere prior to the report fully displays?

I would try add Parameter data types to the subreport's record source for the dates.

Does this query return records when the report is open?

SELECT Count(SV00300.Service_Call_ID) AS CountOfService_Call_ID, SV00300.Type_of_Problem
FROM SV00300
GROUP BY SV00300.Type_of_Problem
HAVING (((SV00300.DATE1)>=[forms]![frmWorkOrderlog]![txtDatefrom] And (SV00300.DATE1)<=[forms]![frmWorkOrderlog]![txtDateto]));

Duane

---

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Art Lorenzini via Groups.Io <dbalorenzini=yahoo.com@groups.io>
Sent: Tuesday, March 24, 2020 5:10 PM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: [MSAccessProfessionals] Subreport not returning rows

 

I have a report that works fine. But I pulled in a query from the object viewer which createa a sub report but its only retrieving one row. If you run the query it returns multiple rows. I am I missing a setting in the sub report? Then I figured out that there was a master/child link between the main report and the sub report. I removed that and it worked fine. 

But then I ran into another issue, I changed the sub report query to the following:

SELECT Count(SV00300.Service_Call_ID) AS CountOfService_Call_ID, SV00300.Type_of_Problem
FROM SV00300
GROUP BY SV00300.Type_of_Problem
HAVING (((SV00300.DATE1)>=[forms]![frmWorkOrderlog]![txtDatefrom] And (SV00300.DATE1)<=[forms]![frmWorkOrderlog]![txtDateto]));

and now the subreport does not return any data.

The main report record source is as follows:
SELECT SV00300.ADRSCODE AS [Unit No], SV00300.CUSTNAME AS [Tenant Name], SV00300.Service_Call_ID AS [Work Order ID], SV00300.Service_Description, SV00300.DATE1 AS [Work Order Date], SV00300.Type_of_Problem AS Type, SV00300.Status_of_Call AS Status, SV00300.Technician
FROM SV00300
WHERE DATE1>=[forms]![frmWorkOrderlog]![txtDatefrom] And
DATE1<=[forms]![frmWorkOrderlog]![txtDateto] And
Status_of_Call=Nz([forms]![frmWorkOrderlog]![cbostatus],Status_of_Call);

If I leave the dates hardcoded in the sub report it returns rows.

Any ideas?

Thank you 

Art Lorenzini, 
Sioux Falls, SD