Re: [MSAccessProfessionals] Subreport not returning rows

I tried adding parameter and now when the report kicks off it prompts me for an From and To date....

With Warm Regards,

 

Arthur D. Lorenzini

IT System Manager

Cheyenne River Housing Authority

Wk.(605)964-4265  Ext. 130

Fax (605)964-1070

"Valar Dohaeris"

On Wednesday, March 25, 2020, 09:25:08 PM CDT, Duane Hookom <duanehookom@hotmail.com> wrote:

Hi Art,

When you are in design view of your query, click the parameters icon near the top right. Enter you form/control and select Date With Time.

Duane

---

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Art Lorenzini via Groups.Io <dbalorenzini=yahoo.com@groups.io>
Sent: Wednesday, March 25, 2020 3:11 PM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Subreport not returning rows

 

Duane,

Sorry it took so long o reply, Putting out fires all day. The form frmWorkOrderLog stays open during the whole process. You have to click a button to close it. The user enters the start date in text field txtDateFrom and the end date in text field txtDateFrom and click a button that execute the following code:

Private Sub cmdReport_Click()
    On Error GoTo Err_cmdReport_Click

    Dim stDocName As String

    stDocName = "rptWOLogReport"
   
'Check values are entered into Date From and Date To text boxes
'if so run report or cancel request

    If Len(Me.txtdatefrom & vbNullString) = 0 Or Len(Me.txtDateTo & vbNullString) = 0 Then
        MsgBox "Please ensure that a report date range is entered into the form", _
               vbInformation, "Required Data..."
        Exit Sub
    Else
        DoCmd.OpenReport stDocName, acPreview
    End If
Exit_cmdReport_Click:
    Exit Sub

Err_cmdReport_Click:
    MsgBox Err.Description
    Resume Exit_cmdReport_Click
End Sub

If I hard code the to and from dates into the query:

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]));

It works. But when I change it to:

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]));

Is does not return anything.

Can you explain how I use Parameter data types in my query. I don't think I have encountered them before.

With Warm Regards,

 

Arthur D. Lorenzini

IT System Manager

Cheyenne River Housing Authority

Wk.(605)964-4265  Ext. 130

Fax (605)964-1070

"Valar Dohaeris"

On Tuesday, March 24, 2020, 06:11:15 PM CDT, Duane Hookom <duanehookom@hotmail.com> wrote:

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