Solved

How count number of days represented in detail section of report

Posted on 2011-09-03
23
468 Views
Last Modified: 2013-11-05
I have a report that shows records of parts produced in a date range.  But in the detail section of the report I need to show the number of distinct days represented.   In other words if the report shows records produced on Friday, August 19th and records produced on Monday, August 22nd and records produced on Monday, August 23rd... then the answer should be 3.

What would the syntax be for the control source of this field?

--Steve

0
Comment
Question by:SteveL13
  • 10
  • 7
  • 6
23 Comments
 
LVL 6

Expert Comment

by:c1nmo
ID: 36478040
e.g. DateDiff("d",#01/01/2011#,#01/02/2011#)
0
 

Author Comment

by:SteveL13
ID: 36478065
That won't work.  Remember, the report shows records that have dates listed like:

Record #1   8/19/2011      blah, blah, blah
Record #1   8/22/2011      blah, blah, blah
Record #1   8/23/2011      blah, blah, blah


So the answer has to be 3 but I don't know how to code it.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36478072
if you have multiple records showing multiple dates of Aug 19, Aug 22, Aug 23

you will need something like this to have a distinct count of days

select count(*) from (select distinct [datefield] from tableName)


need more info about the records from table

upload a copy of db if you have trouble figuring out to display the count of unique days
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36478099
for the sample you posted , try the dcount() function

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36478102
or you can use vba codes using recordset in the format or print event of the detail section of the report
0
 

Author Comment

by:SteveL13
ID: 36478103
There ARE multiple records showing dates in the detail section of the report.  But I need to show the number of distinct dates in the footer section of the report.  The grouping on the report is based on a field named "Molder".  The query under the report is named "qryDailyProdByMoldSec" if that helps.

So something like...

= count(*) from (select distinct [date] from qryDailyProdByMoldSec) where [Molder]= [txtMolder]     or something like that maybe?



0
 

Author Comment

by:SteveL13
ID: 36478130
So in the onprint event of the footer section maybe something like this?  

Me.txtDayCount = DCount("Date", "report detail section here", "Molder = me.txtMolder")

???

--Steve
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36478134
Me.txtDayCount = DCount("[Date]", "qryDailyProdByMoldSec", "Molder =" & me.txtMolder)
0
 
LVL 6

Expert Comment

by:c1nmo
ID: 36478237
Got this to work:

=eCount("Date","qryDailyProdByMoldSec","molder= '" & [molder] & "'",True)

Using this guys ecount udf:

http://allenbrowne.com/ser-66.html
0
 

Author Comment

by:SteveL13
ID: 36478307
I tried...

=eCount("Date","qryDailyProdByMoldSec","molder= '" & [molder] & "'",True)

Using this guys ecount udf:

http://allenbrowne.com/ser-66.html

But I get an error message (twice) that reads:  Ecount Error 3061.  Too few parameters.  Expected 2.
0
 
LVL 6

Expert Comment

by:c1nmo
ID: 36478327
The value in square brackets is grouped value control on the form in my sample, maybe it should be txtMolder for you?

=eCount("Date","qryDailyProdByMoldSec","molder= '" & [txtMolder] & "'",True)

0
 

Author Comment

by:SteveL13
ID: 36478328
Using Me.txtDayCount = DCount("[Date]", "qryDailyProdByMoldSec", "Molder =" & me.txtMolder)

I get another error...

 clip 1 clip2
0
 

Author Comment

by:SteveL13
ID: 36478333
To c1nmo... I did try

=eCount("Date","qryDailyProdByMoldSec","molder= '" & [txtMolder] & "'",True)

and received the same error twice.
0
 
LVL 6

Expert Comment

by:c1nmo
ID: 36478343
I've attached the working sample I've been messing with

 Database3.accdb
0
 

Author Comment

by:SteveL13
ID: 36478362
I can see that your example does work but for whatever reason I can't get it to work.

=eCount("Date","qryDailyProdByMoldSec","molder= '" & [Molder] & "'",True)

Its almost like I need to replace qryDailyProdByMoldSec with the detail section of the report.

0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 250 total points
ID: 36478376

so the field Molder is Text data type

use this



Me.txtDayCount = DCount("[Date]", "qryDailyProdByMoldSec", "Molder ='" & me.txtMolder & "'")




0
 
LVL 6

Assisted Solution

by:c1nmo
c1nmo earned 250 total points
ID: 36478381
In the error screenshot above (36478328) it appears you don't have single quotes around the value from [txtMolder]?  In reply 36478362 you have [Molder] instead of Me.txtMolder?
0
 

Author Comment

by:SteveL13
ID: 36478412
Using...   Me.txtDayCount = DCount("[Date]", "qryDailyProdByMoldSec", "Molder ='" & me.txtMolder & "'")
 I get...



 clip3
0
 

Author Comment

by:SteveL13
ID: 36478435
Ok, I was able to get this to work by adding the txtMolder field to the detail section of the report.

Me.txtDayCount = DCount("[Date]", "qryDailyProdByMoldSec", "Molder ='" & me.txtMolder & "'")

But what I need is the number of unique dates, not the total date count.  So if the detail section shows...

Record #1   8/19/2011      blah, blah, blah
Record #2   8/22/2011      blah, blah, blah
Record #3   8/23/2011      blah, blah, blah
Record #4   8/19/2011      blah, blah, blah
Record #5   8/23/2011      blah, blah, blah

then the result should be 3.  Not 5.





0
 
LVL 6

Expert Comment

by:c1nmo
ID: 36478442
I even tried changing my sample renaming mydate to date in case it was a keyword problem but it still works.
Are you getting an error or just no output?
0
 

Author Comment

by:SteveL13
ID: 36478481
c1nmo... getting an error
0
 
LVL 6

Expert Comment

by:c1nmo
ID: 36478562
ref 36478435, can you not use eCount at this stage
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36479149
upload a copy of the db...
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question