I have tried:

IIF([Project Status]="disapproved",count[project status],"")

IIF([Project status]="disapproved",(count[project status]),"")

IIF([Project status]="disapproved",count([project status],""))


And a couple dozen other combinations.

Can't you use a count in an IIF statement?

Thanks for the help!!!!!

I assume you are trying to get the number of 'disapproved' records.
Where are you trying to do this?

If you are in a report you might have...

=sum(IIF([Project Status]="disapproved",1,0))

Could you provide a little more detail about what you are trying to accomplish & perhaps the full SQL text of the query?

If you want a count of projects in each status then perhaps this:

SELECT [Project Status], Count([projectnumber])as ProjectStatusCount
FROM tablename
GROUP by [Project Status]

I am just trying to get a count of the disapproved records in the query.

I don't need to know the number of approved projects.

Thanks!!

You could go this route:

SELECT Count(tblProjects.projectnumber) AS CountOfprojectnumber
FROM tblProjects
GROUP BY tblProjects.[project status]
HAVING (((tblProjects.[project status])="disapproved"));

Right now, my sql looks like this but every time I put count in there it blows up:

SELECT [xx_capital query formII 2013-2015].[Project Status], [xx_capital query formII 2013-2015].[Qualifying Cost], [xx_capital query formII 2013-2015].[State Financial Participation], IIf([project status]="disapproved",[project status],"") AS Disapproved
FROM [xx_capital query formII 2013-2015];

Thanks!

You do a count by using an aggregate query as I showed in my earlier post. If you want details of each record whose project status is disapproved then this is what you need (but it does not give you a count)



SELECT [xx_capital query formII 2013-2015].[Project Status], [xx_capital query formII 2013-2015].[Qualifying Cost], [xx_capital query formII 2013-2015].[State Financial Participation]
FROM [xx_capital query formII 2013-2015]
WHERE [Project Status]="disapproved"

Perhaps I am just confused as to what you want the query results to show. Could you provide an example output that you hope to get from the query?

Thanks!

Ultimately, I just want a textbox on the bottom of a form with the number of disapproved projects. (just a number, like 18)

I'll go back and look at your aggregate example a little more.

Thanks so much!!!!

If you just want to display a count in a text box on a form, they you will want to use a DCount() function in the control source of the textbox


=DCount("*","[xx_capital query formII 2013-2015]","[Project Status]='disapproved'")

Thanks! That did the trick!

You're welcome