Need help counting records from a query based on certain criteria and return the value on a report

[AnnaHansen]

Hi All,

This seems like it should be easy but I can't for the life of me figure out how to do it.

I have a query called "Performance Measure - Days" that calculates how many days it took an employee to complete an assignment. It has the following fields:

Tracking #
Date Assigned
Date Completed
Days: [Date Assigned]-[Date Completed]

My report has the "Performance Measure - Days" query as the control source. It has two text fields, which I want to show the number of assignments completed in 90 days or less, and the number of assignments completed in more than 90 days.

I tried having the following as my control source for the text field for assignments completed in more than 90 days:
=DCount("[Tracking #]","Performance Measure - Days","[Days] > 90")

This returns #error which I cannot figure out how to fix.

I have tried breaking up the query into two:

"Performance Measure - 90 Days or Less"
Tracking #
Date Assigned
Date Completed
90 Days or Less: [Date Assigned]-[Date Completed], Criteria <=90

Performance Measure - More than 90 Days
Tracking #
Date Assigned
Date Completed
More than 90 Days: [Date Assigned]-[Date Completed], Criteria >90

Then I set my text field control sources to:
=Count([Performance Measure - 90 Days or Less]![90 Days or Less]), and
=Count([Performance Measure - More than 90 Days]![More than 90 Days])

But this will only return a value for one text box, depending on which query I set as the control source for the report. The opposite text box throws a dialog box asking me for the value instead of using the query results.

 

[Joe4]

I tried having the following as my control source for the text field for assignments completed in more than 90 days:
=DCount("[Tracking #]","Performance Measure - Days","[Days] > 90")

This returns #error which I cannot figure out how to fix.

I just tried re-creating your scenario, and this works perfectly fine for me.
What exactly does the error say?
Which section on your report are you trying to place this text box in?

Do you still get an error if you leave the criteria off of your DCOUNT function, i.e.:

=DCount("[Tracking #]","Performance Measure - Days")

Also check in your query at your "Days" calculation field. Is it returning any errors for any records?

 

[AnnaHansen]

I just tried re-creating your scenario, and this works perfectly fine for me.
What exactly does the error say?
Which section on your report are you trying to place this text box in?

Do you still get an error if you leave the criteria off of your DCOUNT function, i.e.:

=DCount("[Tracking #]","Performance Measure - Days")

Also check in your query at your "Days" calculation field. Is it returning any errors for any records?

Hi, thanks for your reply!

When I preview the report, the field just says "#error" instead of returning a result. I don't get a dialog box or anything like that. I am putting the field in the detail section of the report.

Same error if I take off the criteria. here's a copy paste just to ensure no typos:

=DCount("[Tracking #]","Performance Measure - Days")

The datasheet view of the query returns the correct data with no errors.

 

[AnnaHansen]

I must have had a typo - I re-entered the formulas and now the report is working. Thanks for your help!