DCount with multiple conditions

[Guest]

I have a Group By query based on the [Cycle Info] table.
This query counts the number of fresh cycles that were
completed, resulted in diffrent outcomes in each year. In the
same query, I want to see the number of frozen cycles
performed during the years in the query. The frozen cycle
records are in the CylceInfoFRZ table and I am accessing
this table through the FRZOut1 query. I am using the
DCount function to do this. Using information posted on this
forum, I have succeeded to include a condition into the
DCount function so that it will count records for the
corresponding years in my query:
FRZcyc: DCount("[FRZID]","FRZOut1","Year([CycleStart])=" & Year([Cycle
Info].[CycleStartDate]))
However, I cannot add another condition, eg. TreatOutID=11, to
this function (TreatOutID is a numeric field in the FRZOut1
query.
I thank for your help.
AS
The following is the full SQL statement of my current query:
SELECT Year([CycleStartDate]) AS [Cycle Year], Count([Cycle Info].CycleID)
AS Cycles, Sum(IIf([TrfNo]>0,1,0)) AS Trf,
DCount("[FRZID]","FRZOut1","Year([CycleStart])=" & Year([Cycle
Info].[CycleStartDate])) AS FRZcyc
FROM [Patient Info] INNER JOIN [Cycle Info] ON ([Patient Info].PatID =
[Cycle Info].PatID) AND ([Patient Info].PatID = [Cycle Info].PatID)
GROUP BY Year([CycleStartDate])
ORDER BY Year([CycleStartDate]), Count([Cycle Info].CycleID);

 

[Douglas J. Steele]

FRZcyc: DCount("[FRZID]","FRZOut1","Year([CycleStart])=" & Year([Cycle
Info].[CycleStartDate]) & " And [TreatOutId]=11")

 

[Guest]

Thank you Doug. This works!
AS

FRZcyc: DCount("[FRZID]","FRZOut1","Year([CycleStart])=" & Year([Cycle
Info].[CycleStartDate]) & " And [TreatOutId]=11")


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have a Group By query based on the [Cycle Info] table.
This query counts the number of fresh cycles that were
completed, resulted in diffrent outcomes in each year. In the
same query, I want to see the number of frozen cycles
performed during the years in the query. The frozen cycle
records are in the CylceInfoFRZ table and I am accessing
this table through the FRZOut1 query. I am using the
DCount function to do this. Using information posted on this
forum, I have succeeded to include a condition into the
DCount function so that it will count records for the
corresponding years in my query:
FRZcyc: DCount("[FRZID]","FRZOut1","Year([CycleStart])=" & Year([Cycle
Info].[CycleStartDate]))
However, I cannot add another condition, eg. TreatOutID=11, to
this function (TreatOutID is a numeric field in the FRZOut1
query.
I thank for your help.
AS
The following is the full SQL statement of my current query:
SELECT Year([CycleStartDate]) AS [Cycle Year], Count([Cycle Info].CycleID)
AS Cycles, Sum(IIf([TrfNo]>0,1,0)) AS Trf,
DCount("[FRZID]","FRZOut1","Year([CycleStart])=" & Year([Cycle
Info].[CycleStartDate])) AS FRZcyc
FROM [Patient Info] INNER JOIN [Cycle Info] ON ([Patient Info].PatID =
[Cycle Info].PatID) AND ([Patient Info].PatID = [Cycle Info].PatID)
GROUP BY Year([CycleStartDate])
ORDER BY Year([CycleStartDate]), Count([Cycle Info].CycleID);