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
> Count Hidden Rows In A Report, Access 2013    
 
   
snekker
post May 23 2019, 02:12 PM
Post#1



Posts: 111
Joined: 7-June 08



My report shows projects and tasks, with their statuses. The report is grouped by projects, with tasks in the Detail section. When exported to PDF (or print preview), each project has its own landscape-view page. Tasks that are 100% complete are hidden from the report so it only displays remaining work for the project. How can I show a statement for each project that shows how many tasks are hidden?

For example, "7 completed tasks are hidden"

I tried using DCount with the source table to get the total number of tasks before hiding the completed ones, and ran it from the group header's Paint event, but that slowed the report considerably. I think it was running each time it wrote a record into the detail section rather than once for the whole section. I might just be doing it from the wrong event.
Go to the top of the page
 
mike60smart
post May 26 2019, 10:39 AM
Post#2


UtterAccess VIP
Posts: 13,181
Joined: 6-June 05
From: Dunbar,Scotland


You could use an If statement.

=IIf([DateCompleted] Is Not Null,1,0)

Then Total this Control in the Footer


--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
tina t
post May 26 2019, 05:01 PM
Post#3



Posts: 5,955
Joined: 11-November 10
From: SoCal, USA


QUOTE
Tasks that are 100% complete are hidden from the report so it only displays remaining work for the project.

well, i'm not sure of your use of the term "hidden". if you mean the completed records are excluded from the report's RecordSource, then you'll have to count them with an "outside" action, like a DCount() function. but if the completed records are included in the report's RecordSource, then it should be simple enough to count them - doesn't matter if the user sees them in the Detail section or not.

there must be a way that you identify or "flag" a task record as completed. a "Complete" status, perhaps? for the purpose of writing an expression, i'll assume that's how you're doing it. also, i'd suggest using the Footer section of the report if you can. i've had problems counting detail records from the Header section in the past - though i've only used .mdb versions of Access, and you may not experience the same issue in your newer .accdb version.

anyway, try adding an unbound textbox control to the Footer section of the report, and put the following expression in the control's ControlSource property, as

=Sum(IIf([MyStatusFieldName] = "Complete", 1, 0))

if your status field holds a number value (such as a foreign key from a tblStatuses) then use the correct number value that means "Complete", instead of using the text itself. if it works for you, then you can add the text you posted, as

=Sum(IIf([MyStatusFieldName] = "Complete", 1, 0)) & " completed tasks are hidden"

hth
tina
This post has been edited by tina t: May 26 2019, 05:03 PM

--------------------
"the wheel never stops turning"
Go to the top of the page
 
snekker
post Jun 3 2019, 10:06 AM
Post#4



Posts: 111
Joined: 7-June 08



To clarify, I'm excluding those records in the report's source query, so "hidden" was not the correct word. I'll try using the footer column for the formula.
Go to the top of the page
 
tina t
post Jun 3 2019, 02:03 PM
Post#5



Posts: 5,955
Joined: 11-November 10
From: SoCal, USA


as i mentioned before, if the records are not part of the report's RecordSource, then you'll need to use a domain aggregate function such as DCount(). if you're not familiar with these functions, suggest you start by reading up on them in Help, so you'll understand how the arguments work.

and btw, you'll want to put the unbound textbox in the Group footer section, not the report footer - i should have been more specific in my previous post, sorry.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
snekker
post Jun 6 2019, 06:20 AM
Post#6



Posts: 111
Joined: 7-June 08



I added an unbound text box named txtTaskCount to the group header with the Control Source set to,
CODE
IIf(Sum(IIf([PercentComplete] = 1, 1, 0)) > 1, Sum(IIf([PercentComplete] = 1, 1, 0)) & " completed tasks are hidden", "")

I removed the "<1 or is Null" clause from the query, and hid the completed tasks from my filtered report from the Detail_Format event:
CODE
If Screen.ActiveReport.Name = "Filtered Report" Then
    txtTaskCount.Visible = True
    If Me.txtPercentComplete = 1 Then
        Me.Detail.Visible = False
    Else
        Me.Detail.Visible = True
    End If
Else
    txtTaskCount.Visible = False
End If

This is sort of working, but it's a little slow and doesn't filter the completed tasks in report view - Only print preview or when it's exported to PDF. I suspect it would be faster if I could calculate the value of txtTaskCount from VBA rather than in the unbound text box, but I'm having some trouble figuring out which event to put that is so it displays when the report opens in report view, and also when I export it to PDF.
This post has been edited by snekker: Jun 6 2019, 06:21 AM
Go to the top of the page
 
tina t
post Jun 6 2019, 03:05 PM
Post#7



Posts: 5,955
Joined: 11-November 10
From: SoCal, USA


QUOTE
To clarify, I'm excluding those records in the report's source query, so "hidden" was not the correct word.

QUOTE
I ... hid the completed tasks from my filtered report from the Detail_Format event

okay, hon, you need to make up your mind. if the records are not included in the report's RecordSource, then you need to use a domain aggregate function, as i said before. if the records are included in the report's RecordSource, which you now seem to be indicating, then don't use a domain aggregate function. instead, as i said before

QUOTE
try adding an unbound textbox control to the Footer section of the report, and put the following expression in the control's ControlSource property, as

=Sum(IIf([MyStatusFieldName] = "Complete", 1, 0))

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
snekker
post Jun 10 2019, 06:54 AM
Post#8



Posts: 111
Joined: 7-June 08



I think you confused yourself, sweetie. If you're going to quote me, you can't edit out the part that makes the quote make sense.

Me:
QUOTE
I removed the "<1 or is Null" clause from the query, and hid the completed tasks from my filtered report from the Detail_Format event:


Not me:
QUOTE
I ... hid the completed tasks from my filtered report from the Detail_Format event
Go to the top of the page
 
tina t
post Jun 10 2019, 01:57 PM
Post#9



Posts: 5,955
Joined: 11-November 10
From: SoCal, USA


okay, i've given you the best help i can. good luck with your project. tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th June 2019 - 11:43 AM