Dcount now gives #Error

[Terri Storer]

I have a database linked to a Data Source (a case management system) using ODBC for the purpose of creating reports. Nothing has changed between the data source, ODBC, or MS Access.

In one of the reports, I have a footer with Text Boxes to capture a DCount based on staff lastname for the number of cases assigned to an attorney. As attorneys come and go, I add them to this list - copy the label and text box and update the Control Source: =DCount syntax to the new attorney's last name OR simply rename a label and control source of an attorney who has left.

This has worked flawlessly UNTIL this last time.
NOW, I get the correct count for the new attorney I added and all the "existing attorneys" on the list return an #Error. I made NO changes to control source for the "existing attorneys." All other text box fields in the footer with DCounts work just fine.

Thoughts?
I appreciate any guidance.
Terri Storer

Control Source: =DCount("*","qryCLOPENList"," [staff_ln] = 'Ealey' ")

Snippet of report footer.

 

[plog]

You showed us the snippet that worked. Now show us a snippet that doesn't.

And honestly, you're doing too much work for this report. There's no need for you to add a new label and input for each lawyer. Make a query from qryCLOPENLIST that lists attorneys by case count:

SELECT COUNT[staff_In] AS Cases, staff_In FROM qryCLOPENList GROUP BY staff_In;

Make a report using that and then use it as a subreport on the report you have now. You will never need to add/edit/subtract attorneys manually again. Further, it avoids this error you are having now entirely.

 

[theDBguy]

Hi Terri,

Welcome to AWF!

Just curious, are all those boxes with #Error in them using DCount()? Or, are any of them might be using Sum()?

 

[Terri Storer]

They are all using DCount and are identical to the one provided, except for the last name. I did realize that I had added the new user to the data source and did NOT Reinitialize SQL on that side prior to running this report in Access. That fixed it.
HOWEVER, I am very grateful to the suggestion for an easier option !!!!