UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Query Returns Only One Row, Access 2016    
 
   
alorenzini
post Mar 24 2020, 03:47 PM
Post#1



Posts: 818
Joined: 21-September 07
From: South Dakota


I have a report that works fine. But I pulled in a query from the object viewer which create 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?

Thank you,

Art Lorenzini
Sioux Falls SD

--------------------
Thanks

Art Lorenzini
Sioux Falls, SD
Go to the top of the page
 
theDBguy
post Mar 24 2020, 03:53 PM
Post#2


UA Moderator
Posts: 77,704
Joined: 19-June 07
From: SunnySandyEggo


Hi Art. Did you link the subreport? Is the subreport grouping the data?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
alorenzini
post Mar 24 2020, 04:02 PM
Post#3



Posts: 818
Joined: 21-September 07
From: South Dakota


Yes I found that to be the issue as soon as I sent the question. 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.


--------------------
Thanks

Art Lorenzini
Sioux Falls, SD
Go to the top of the page
 
Larry Larsen
post Mar 25 2020, 03:40 AM
Post#4


UA Editor + Utterly Certified
Posts: 24,521
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi Art

Could be that you need to apply a format to the date controls..

CODE
Format Date (##) in SQL statement

Function fSQLDate(varDate, Optional blnDelim As Boolean = True)
On Error Resume Next

    If IsNull(varDate) Then
        fSQLDate = Null
    ElseIf IsDate(varDate) And Not blnDelim Then
        fSQLDate = Format(varDate, "yyyy\-mm\-dd")
    ElseIf IsDate(varDate) Then
        fSQLDate = Format(varDate, "\#yyyy\-mm\-dd\#")
    End If
    
End Function

"[assUpdated] >= " & fSQLDate(Me.BeginningDate) & " and ...

thumbup.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
projecttoday
post Mar 25 2020, 04:46 AM
Post#5


UtterAccess VIP
Posts: 11,782
Joined: 10-February 04
From: South Charleston, WV


HAVING is for aggregate functions. Use WHERE instead.
Why the same table for the main report and the subreport?

You're saying

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>=#01/25/2020# And
DATE1<=#03/25/2020# And
Status_of_Call=Nz([forms]![frmWorkOrderlog]![cbostatus],Status_of_Call)

works. And

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

doesn't?

--------------------
Robert Crouser
Go to the top of the page
 
alorenzini
post Mar 25 2020, 06:11 PM
Post#6



Posts: 818
Joined: 21-September 07
From: South Dakota


The SubReport Query is.

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

This query is a group by which contents will change each time it runs...

--------------------
Thanks

Art Lorenzini
Sioux Falls, SD
Go to the top of the page
 
projecttoday
post Mar 25 2020, 07:29 PM
Post#7


UtterAccess VIP
Posts: 11,782
Joined: 10-February 04
From: South Charleston, WV


Okay.

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)>= #01/01/2018# And (SV00300.DATE1)<= #03/26/2020#));

works and

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

does not work?

What is the format of txtDatefrom and txtDateto? (Short date?) What is the contents of txtDatefrom and txtDateto?

--------------------
Robert Crouser
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    29th March 2020 - 03:10 AM