Count Function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

S Jackson - 06 Sep 2005 16:10 GMT

I have a report whose query contains the following criteria:

Between [Forms]![fdlgRpts]![begdate] And [Forms]![fdlgRpts]![enddate])

Within the report I have a field that counts the following:

=Count([AssignDate])

I want to change this field to only count [AssignDate] that fall Between
[Forms]![fdlgRpts]![begdate] And [Forms]![fdlgRpts]![enddate]

I tried the following and it did not work:
=(Count([AssignDate]) Between [Forms]![fdlgRpts]![begdate] And
[Forms]![fdlgRpts]![enddate])

How do I correct this? TIA
S. Jackson

Reply to this Message

Duane Hookom - 06 Sep 2005 16:32 GMT

If your report is limited by AssignDate between the dates on the form then
=Count(AssignDate) should work.
What was wrong with the number your report is displaying?

Signature

Duane Hookom
MS Access MVP
--

>I have a report whose query contains the following criteria:
>
[quoted text clipped - 13 lines]
> How do I correct this? TIA
> S. Jackson

Reply to this Message

S Jackson - 06 Sep 2005 17:04 GMT

Sorry, I didn't clarify things correctly. The query has two criteria:

SELECT tblCaseInfo.CaseId, tblCaseInfo.DHSNo, tblCaseInfo.CaseName,
tblCaseInfo.Region, tblCaseInfo.NoticeDate, tblCaseInfo.AppealDate,
tblCaseInfo.AssignDate, tblCaseInfo.DHSAttny, tblCaseInfo.Action,
tblHearings.HrgStart, tblHearings.PFDDate, tblStatus.CSADate,
tblStatus.ClosedDate, tblStatus.DispositionType
FROM ((((tblRepresentatives RIGHT JOIN tblCaseInfo ON tblRepresentatives.ID
= tblCaseInfo.RepID) LEFT JOIN tblDiscRec ON tblCaseInfo.CaseId =
tblDiscRec.CaseID) LEFT JOIN tblDiscSent ON tblCaseInfo.CaseId =
tblDiscSent.CaseID) LEFT JOIN tblHearings ON tblCaseInfo.CaseId =
tblHearings.CaseId) LEFT JOIN tblStatus ON tblCaseInfo.CaseId =
tblStatus.CaseId
GROUP BY tblCaseInfo.CaseId, tblCaseInfo.DHSNo, tblCaseInfo.CaseName,
tblCaseInfo.Region, tblCaseInfo.NoticeDate, tblCaseInfo.AppealDate,
tblCaseInfo.AssignDate, tblCaseInfo.DHSAttny, tblCaseInfo.Action,
tblHearings.HrgStart, tblHearings.PFDDate, tblStatus.CSADate,
tblStatus.ClosedDate, tblStatus.DispositionType
HAVING (((tblCaseInfo.AssignDate) Between [Forms]![fdlgRpts]![begdate] And
[Forms]![fdlgRpts]![enddate])) OR (((tblStatus.ClosedDate) Between
[Forms]![fdlgRpts]![begdate] And [Forms]![fdlgRpts]![enddate]));

In the report, I only want to count the number of cases assigned between
[Forms]![fdlgRpts]![begdate] And [Forms]![fdlgRpts]![enddate].

Do I need to use the DCount function? If so, I am having trouble with the
"Where" portion of it. Here is what I have so far:

=DCount([AssignDate],"qryTrackingRpt",[AssignDate]>[Forms]![fdlgRpts]![begda
te] And [AssignDate]<[Forms]![fdlgRpts]![enddate])

Obviously, this doesn't work because it is counting all [AssignDate] greater
than the [begdate] AND less than [enddate]. I need "Between" [begdate] and
[enddate]. How do I do this?

Thanks.
S. Jackson

> If your report is limited by AssignDate between the dates on the form then
> =Count(AssignDate) should work.
[quoted text clipped - 22 lines]
> > How do I correct this? TIA
> > S. Jackson

Reply to this Message

Duane Hookom - 06 Sep 2005 17:13 GMT

Try add a text box with a control source of:
=Sum(Abs( [AssignDate] Between [Forms]![fdlgRpts]![begdate] And
[Forms]![fdlgRpts]![enddate]) )

Signature

Duane Hookom
MS Access MVP
--

> Sorry, I didn't clarify things correctly. The query has two criteria:
>
[quoted text clipped - 65 lines]
>> > How do I correct this? TIA
>> > S. Jackson

Reply to this Message

S Jackson - 06 Sep 2005 20:26 GMT

Thanks. It worked. Now I have to go look up what it means ;)

S. Jackson

> Try add a text box with a control source of:
> =Sum(Abs( [AssignDate] Between [Forms]![fdlgRpts]![begdate] And
[quoted text clipped - 33 lines]
> > Do I need to use the DCount function? If so, I am having trouble with the
> > "Where" portion of it. Here is what I have so far:

=DCount([AssignDate],"qryTrackingRpt",[AssignDate]>[Forms]![fdlgRpts]![begda

> > te] And [AssignDate]<[Forms]![fdlgRpts]![enddate])
> >
[quoted text clipped - 35 lines]
> >> > How do I correct this? TIA
> >> > S. Jackson

Reply to this Message