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
Duane Hookom said:
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?
--
Duane Hookom
MS Access MVP
--
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
