Re: [MSAccessProfessionals] Subreport not returning rows

Do I get rid of it in the query.... ?

>=[forms]![frmWorkOrderlog]![txtDatefrom] And (SV00300.DATE1)<=[forms]![frmWorkOrderlog]![txtDateto]));

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