Count IIF

[Secret Squirrel]

I want to be able to count the # of records based on two criterias. First I
want to count all records that are in the month of "January" and then only
count the # of records that have a variance of greater than 1 day but less
than 4 days. I want to put this into my control source in a textbox on my
main form. The query detail is on my subform. How would I write the
calculation to do this count/iif?

Here is my query. The variance I'm looking for comes from the expression
called "VOrigDate".

SELECT qryUnionPerformance.RecordID, qryUnionPerformance.VendorID,
qryUnionPerformance.PurchaseOrderNo, qryUnionPerformance.PartNo,
qryUnionPerformance.OrderQty, qryUnionPerformance.RecQty,
qryUnionPerformance.OrigDate, qryUnionPerformance.LastDate,
qryUnionPerformance.RecDate, Format([RecDate],"yyyy") AS Years,
Format([RecDate],"mmmm") AS Months,
DateDiff("d",[OrigDate],[RecDate])-(DateDiff("ww",[OrigDate],[RecDate],7)+DateDiff("ww",[OrigDate],[RecDate],1))
AS VOrigDate,
DateDiff("d",[LastDate],[RecDate])-(DateDiff("ww",[LastDate],[RecDate],7)+DateDiff("ww",[LastDate],[RecDate],1)) AS VLastDate
FROM qryUnionPerformance
WHERE
(((qryUnionPerformance.VendorID)=[Forms]![frmPerformanceMain]![VendorID]) AND
((Format([RecDate],"yyyy"))=[Forms]![frmPerformanceMain]![cboYearSelect]));

 

[Duane Hookom]

You could add a text box to your subform header that contains the calculation
and then reference the subform text box from the main form. The text box in
the subform header could have a control source like:
=Sum(Abs(...Your true/false expression here...))
For the month of January, use:
=Sum(Abs(Month([DateField])=1))

 

[Secret Squirrel]

That works but how do I also add another expression that will only count
records in january that have a value between 2 & 3 from my "VOrigDate"
expression in my query?

You could add a text box to your subform header that contains the calculation
and then reference the subform text box from the main form. The text box in
the subform header could have a control source like:
=Sum(Abs(...Your true/false expression here...))
For the month of January, use:
=Sum(Abs(Month([DateField])=1))

--
Duane Hookom
Microsoft Access MVP

I want to be able to count the # of records based on two criterias. First I
want to count all records that are in the month of "January" and then only
count the # of records that have a variance of greater than 1 day but less
than 4 days. I want to put this into my control source in a textbox on my
main form. The query detail is on my subform. How would I write the
calculation to do this count/iif?

Here is my query. The variance I'm looking for comes from the expression
called "VOrigDate".

SELECT qryUnionPerformance.RecordID, qryUnionPerformance.VendorID,
qryUnionPerformance.PurchaseOrderNo, qryUnionPerformance.PartNo,
qryUnionPerformance.OrderQty, qryUnionPerformance.RecQty,
qryUnionPerformance.OrigDate, qryUnionPerformance.LastDate,
qryUnionPerformance.RecDate, Format([RecDate],"yyyy") AS Years,
Format([RecDate],"mmmm") AS Months,
DateDiff("d",[OrigDate],[RecDate])-(DateDiff("ww",[OrigDate],[RecDate],7)+DateDiff("ww",[OrigDate],[RecDate],1))
AS VOrigDate,
DateDiff("d",[LastDate],[RecDate])-(DateDiff("ww",[LastDate],[RecDate],7)+DateDiff("ww",[LastDate],[RecDate],1)) AS VLastDate
FROM qryUnionPerformance
WHERE
(((qryUnionPerformance.VendorID)=[Forms]![frmPerformanceMain]![VendorID]) AND
((Format([RecDate],"yyyy"))=[Forms]![frmPerformanceMain]![cboYearSelect]));

 

[Duane Hookom]

Your true/false expression can contain multiple true/false expression with "
AND " or " OR " between them
=Sum(Abs(Month([DateField])=1 AND ...some other expression...))

--
Duane Hookom
Microsoft Access MVP

That works but how do I also add another expression that will only count
records in january that have a value between 2 & 3 from my "VOrigDate"
expression in my query?

You could add a text box to your subform header that contains the calculation
and then reference the subform text box from the main form. The text box in
the subform header could have a control source like:
=Sum(Abs(...Your true/false expression here...))
For the month of January, use:
=Sum(Abs(Month([DateField])=1))

--
Duane Hookom
Microsoft Access MVP

I want to be able to count the # of records based on two criterias. First I
want to count all records that are in the month of "January" and then only
count the # of records that have a variance of greater than 1 day but less
than 4 days. I want to put this into my control source in a textbox on my
main form. The query detail is on my subform. How would I write the
calculation to do this count/iif?

Here is my query. The variance I'm looking for comes from the expression
called "VOrigDate".

SELECT qryUnionPerformance.RecordID, qryUnionPerformance.VendorID,
qryUnionPerformance.PurchaseOrderNo, qryUnionPerformance.PartNo,
qryUnionPerformance.OrderQty, qryUnionPerformance.RecQty,
qryUnionPerformance.OrigDate, qryUnionPerformance.LastDate,
qryUnionPerformance.RecDate, Format([RecDate],"yyyy") AS Years,
Format([RecDate],"mmmm") AS Months,
DateDiff("d",[OrigDate],[RecDate])-(DateDiff("ww",[OrigDate],[RecDate],7)+DateDiff("ww",[OrigDate],[RecDate],1))
AS VOrigDate,
DateDiff("d",[LastDate],[RecDate])-(DateDiff("ww",[LastDate],[RecDate],7)+DateDiff("ww",[LastDate],[RecDate],1)) AS VLastDate
FROM qryUnionPerformance
WHERE
(((qryUnionPerformance.VendorID)=[Forms]![frmPerformanceMain]![VendorID]) AND
((Format([RecDate],"yyyy"))=[Forms]![frmPerformanceMain]![cboYearSelect]));

 

[Secret Squirrel]

Perfect! Thanks!

Your true/false expression can contain multiple true/false expression with "
AND " or " OR " between them
=Sum(Abs(Month([DateField])=1 AND ...some other expression...))

--
Duane Hookom
Microsoft Access MVP

That works but how do I also add another expression that will only count
records in january that have a value between 2 & 3 from my "VOrigDate"
expression in my query?

You could add a text box to your subform header that contains the calculation
and then reference the subform text box from the main form. The text box in
the subform header could have a control source like:
=Sum(Abs(...Your true/false expression here...))
For the month of January, use:
=Sum(Abs(Month([DateField])=1))

--
Duane Hookom
Microsoft Access MVP


:

I want to be able to count the # of records based on two criterias. First I
want to count all records that are in the month of "January" and then only
count the # of records that have a variance of greater than 1 day but less
than 4 days. I want to put this into my control source in a textbox on my
main form. The query detail is on my subform. How would I write the
calculation to do this count/iif?

Here is my query. The variance I'm looking for comes from the expression
called "VOrigDate".

SELECT qryUnionPerformance.RecordID, qryUnionPerformance.VendorID,
qryUnionPerformance.PurchaseOrderNo, qryUnionPerformance.PartNo,
qryUnionPerformance.OrderQty, qryUnionPerformance.RecQty,
qryUnionPerformance.OrigDate, qryUnionPerformance.LastDate,
qryUnionPerformance.RecDate, Format([RecDate],"yyyy") AS Years,
Format([RecDate],"mmmm") AS Months,
DateDiff("d",[OrigDate],[RecDate])-(DateDiff("ww",[OrigDate],[RecDate],7)+DateDiff("ww",[OrigDate],[RecDate],1))
AS VOrigDate,
DateDiff("d",[LastDate],[RecDate])-(DateDiff("ww",[LastDate],[RecDate],7)+DateDiff("ww",[LastDate],[RecDate],1)) AS VLastDate
FROM qryUnionPerformance
WHERE
(((qryUnionPerformance.VendorID)=[Forms]![frmPerformanceMain]![VendorID]) AND
((Format([RecDate],"yyyy"))=[Forms]![frmPerformanceMain]![cboYearSelect]));