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: 820
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,736
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: 820
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,535
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,833
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: 820
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,833
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
 
alorenzini
post Mar 30 2020, 12:01 PM
Post#8



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


They are both short dates. Ex. 03/23/2020 To 03/27/2020. I am sure its something simple. The subreport query is losing track of the date range from the main report query. Do I have to maybe pas the date range to the sub report via VBA? I think there has to be an easier solution. But at this point I am willing to try any thing.

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

Art Lorenzini
Sioux Falls, SD
Go to the top of the page
 
projecttoday
post Mar 30 2020, 01:17 PM
Post#9


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


What does the subreport query have to do with the main report query?
Change HAVING to WHERE.
I agree it's probably not interpreting the dates correctly. Unfortunately, I'm not quite expert with that. Larry?
Posting a mock-up might help.

--------------------
Robert Crouser
Go to the top of the page
 
alorenzini
post Mar 30 2020, 01:55 PM
Post#10



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


Sorry but after all that, the darn thing started to work. I don't know what triggered it.

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

Art Lorenzini
Sioux Falls, SD
Go to the top of the page
 
Jeff B.
post Mar 30 2020, 02:00 PM
Post#11


UtterAccess VIP
Posts: 10,411
Joined: 30-April 10
From: Pacific NorthWet


?Do they call it 'gremlins' if it makes it work OK?

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    8th April 2020 - 11:28 PM