[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

How count number of days represented in detail section of report

Posted on 2011-09-03
23
Medium Priority
?
491 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

What’s Wrong with Your Cloud Strategy ?

Even as many CIOs are embracing a cloud-first strategy, the reality is that moving to the cloud is a lengthy process and the end-state is likely to be a blend of multiple clouds—public and private. Learn why multicloud solutions matter in this webinar by Nimble Storage.

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…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

656 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