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
Solved

How count number of days represented in detail section of report

Posted on 2011-09-03
23
462 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
update query 4 25
ERROR 3113 MODULEID NOT UPDATABLE 8 19
Run Stored Procedure uisng ADO 5 21
2 subforms 1 main form 1 13
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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 Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
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 …

828 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