How count number of days represented in detail section of report

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

SteveL13Asked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:

so the field Molder is Text data type

use this



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




0
 
c1nmoCommented:
e.g. DateDiff("d",#01/01/2011#,#01/02/2011#)
0
 
SteveL13Author Commented:
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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Rey Obrero (Capricorn1)Commented:
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
 
Rey Obrero (Capricorn1)Commented:
for the sample you posted , try the dcount() function

0
 
Rey Obrero (Capricorn1)Commented:
or you can use vba codes using recordset in the format or print event of the detail section of the report
0
 
SteveL13Author Commented:
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
 
SteveL13Author Commented:
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
 
Rey Obrero (Capricorn1)Commented:
Me.txtDayCount = DCount("[Date]", "qryDailyProdByMoldSec", "Molder =" & me.txtMolder)
0
 
c1nmoCommented:
Got this to work:

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

Using this guys ecount udf:

http://allenbrowne.com/ser-66.html
0
 
SteveL13Author Commented:
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
 
c1nmoCommented:
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
 
SteveL13Author Commented:
Using Me.txtDayCount = DCount("[Date]", "qryDailyProdByMoldSec", "Molder =" & me.txtMolder)

I get another error...

 clip 1 clip2
0
 
SteveL13Author Commented:
To c1nmo... I did try

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

and received the same error twice.
0
 
c1nmoCommented:
I've attached the working sample I've been messing with

 Database3.accdb
0
 
SteveL13Author Commented:
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
 
c1nmoConnect With a Mentor Commented:
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
 
SteveL13Author Commented:
Using...   Me.txtDayCount = DCount("[Date]", "qryDailyProdByMoldSec", "Molder ='" & me.txtMolder & "'")
 I get...



 clip3
0
 
SteveL13Author Commented:
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
 
c1nmoCommented:
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
 
SteveL13Author Commented:
c1nmo... getting an error
0
 
c1nmoCommented:
ref 36478435, can you not use eCount at this stage
0
 
Rey Obrero (Capricorn1)Commented:
upload a copy of the db...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.