?
Solved

How count number of days represented in detail section of report

Posted on 2011-09-03
23
Medium Priority
?
483 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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 1000 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 1000 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

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

800 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