Conditional Counts in Textbox Controls

[Tom]

Hi. I'm struggling with a DCount in a text box that will generate the
number of items delivered on-time to contract. The query data source
for the report is called _TU_MOCAS_DUE_UPCOMING. Note that it's a
parameter query prompting the user for start/end dates to bound the
report at run-time.

My query SQL:
PARAMETERS [Beginning Due Date] DateTime, [Ending Due Date] DateTime;
SELECT CONTRACTS.CONTRACT_NUMBER AS CONTRACT, FISCAL_YEARS.FISCAL_YEAR
AS FY, SALE_TYPES.SALE_TYPE, CLINS.CLIN, CLINS.SHORT_DESC AS [DESC],
CUSTOMERS.CUSTOMER AS CUST, SIDS.DATE_DUE, CLINS.QTY AS CLIN_QTY,
SIDS.QTY_DUE AS PERIOD_QTY_DUE, PositionToContract(CLINS.CLINS_ID,
[Ending Due Date]) AS PERIOD_PTC
FROM SALE_TYPES INNER JOIN (CUSTOMERS INNER JOIN ((CONTRACTS INNER
JOIN (CLINS INNER JOIN SIDS ON CLINS.CLINS_ID = SIDS.CLINS_ID) ON
CONTRACTS.CONTRACTS_ID = CLINS.CONTRACTS_ID) INNER JOIN FISCAL_YEARS
ON (FISCAL_YEARS.FISCAL_YEARS_ID = CLINS.FISCAL_YEARS_ID) AND
(CONTRACTS.CONTRACTS_ID = FISCAL_YEARS.CONTRACTS_ID)) ON
CUSTOMERS.CUSTOMERS_ID = CLINS.CUSTOMERS_ID) ON
SALE_TYPES.SALE_TYPES_ID = CLINS.SALE_TYPES_ID
WHERE (((SIDS.DATE_DUE) Between [Beginning Due Date] And [Ending Due
Date]));

One of the underlying query fields is generated by running a little
VBA function called PositionToContract. Pass it a CLIN and a date and
it will DSum everything due on that contract line item number by that
date vs. everything delivered by that date to generate a period
"position to contract" value as of that date.

I have counted the number of CLINs due during the period elsewhere.
Now I need to count the number that are currently on-time (i.e.,
[PERIOD_PTC] >= 0) to generate a percent on-time for the period. What
am I screwing up?

My failing text box control source:
=DCount("[PERIOD_PTC]","_TU_MOCAS_DUE_UPCOMING","[PERIOD_PTC] <= 0")

Thanks!

 

[John Spencer]

As far as I know, you can't use a parameter prompt query as the source for
one of the VBA Domain functions.

You can drop the parameters from the query (both in the parameter
declaration and the where clause)and then use the criteria argument to add
in the dates

=DCount("[PERIOD_PTC]","_TU_MOCAS_DUE_UPCOMING","[PERIOD_PTC] <= 0 AND
DateDue Between #1/1/2007# and #1/31/2007#")


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..