Counting text controls on a report that contain a specific value

[VegaLA]

Hi all,
i've been scratching my head over this one for a while now and hope someone can resolve what I thought would be a simple count solution. I have a number of different fields on a report that is based on a query that has just one record but many fields. The value of those fields is numerical and I would like to count the number of fields on the Report that have the value of 0.
I tried putting together a query based on the query which is usued as the Datasource for the report but do not know how to get it to count the zeros as it is a counting fields not values in one particular field.

I hope I explained myself correctly.

Any advice greatly appreciated.

Mitch....

 

[stopher]

Sounds like a very strange database. Maybe you need to do some normalisation?

Anyway, in your query add a new column with the following formula:

ZeroCols: IIF([Col1]=0,1,0)+IIF([Col2]=0,1,0)+IIF([Col3]=0,1,0)...etc

You might need to amend this to deal with blanks/nulls

hth
Chris

 

[VegaLA]

Thanks Chris, that was very prompt.
Cheers !