#Type! error when counting zero records

[scottb]

I have a form with the following sql:
SELECT tblActionItems.DueDate, tblActionItems.EventType, tblActionItems.Description, tblProjects.ProjectName, tblActionItems.ActionItemID, tblActionItems.ProjectID, tblActionItems.ActionType
FROM tblProjects INNER JOIN tblActionItems ON tblProjects.ProjectID = tblActionItems.ProjectID
WHERE (((tblActionItems.ActionType)="Milestone") AND ((Year([DueDate])*53+DatePart("ww",[DueDate]))=Year(Date())*53+DatePart("ww",Date())+1) AND ((tblActionItems.ClosedDate) Is Null));

In the footer of the form, I have a text box with an expression to count instances of ProjectName: =Count([ProjectName]) This is working and if there are no records will result 0.

I have another form with the following sql:
SELECT tblActionItems.ActionItemID, tblProjects.ProjectID, tblProjects.ProjectName, tblActionItems.Description, tblActionItems.BornOn, tblActionItems.Priority, tblActionItems.Impact, tblActionItems.DueDate, tblActionItems.ResourceAssigned, tblActionItems.ActionType, tblActionItems.Status
FROM (tblActionItems INNER JOIN tblProjects ON tblActionItems.ProjectID = tblProjects.ProjectID) INNER JOIN tblPrograms ON tblProjects.ProgramID = tblPrograms.ProgramID
WHERE (((tblActionItems.ActionType)="Decision") AND ((tblActionItems.Status)<>"Completed"));

In the footer of the form, I have a text box with an expression to count instances of ProjectName: =Count([ProjectName]) This is not working and if there are no records result a #Type! Error.

I have tried to locate the differences but have been unable to resolve the error. Thank you for any help!

[HansV]

What happens if you use =Count(*) as Control Source?

[scottb]

If I use =count(*) the sql that is currently working returns an error not 0.
The sql that isn't working returns nothing.

[HansV]

I'm afraid it's too difficult to troubleshoot this from the SQL alone. Could you create a stripped-down copy of the database without sensitive data, zip it and attach the zip file to a reply? Thanks in advance!

[scottb]

Hans,
Sorry for the delay in posting this I was just able to get back to it. Here is a stripped version of the db. I put the forms together to illustrate where the error is occurring. I have tried to get this working for a while and can't find what I'm doing wrong. Thank you very much for your help. -Scott

[HansV]

The difference is that the subform subfrmDecisions has AllowAdditions set to No. As a result, if the query qryActionItemsDecisions doesn't return any records, the subform is completely empty, including the count text box.
If you allow additions and set the control source to =Count(*), you'll get 0.
See the attached version.

Database with errors.zip

[scottb]

Hans, Thank you for your help and time looking at this. It was a small but lingering annoyance and I am grateful to have it resolved.