Sum of calculated field HELP PLEASE

[svgelacio]

I have a calculated text box which i named [Files sum] in my reports detail section. It has a control source of {=IIf([ECV2 date]=1,1,0)+IIf([Pass2 date]=1,1,0)+IIf([TWC2 date]=1,1,0)+IIf([EVF2 Date]=1,1,0)+IIf([Medical2 date]=1,1,0)+IIf([Pic2 date]=1,1,0)+IIf([Police2 date]=1,1,0)+IIf([TT2 Recvd]=1,1,0)+IIf([Med2 Recvd]=1,1,0)} just to add the different field if it has a date on it and make it 0 if its blank. Im concern with the 0 value and wanted to count (sum) it in my report footer. Anyone can help me how to do this? I tried =sum(Iif([Files sum]=0,1,0)) but it returns 0 value, same case with using count(). Please help me on this.

 

[Dennisk]

you need to sum the calculation

SUM(IIf([ECV2 date]=1,1,0)+IIf([Pass2 date]=1,1,0)+IIf([TWC2 date]=1,1,0)+IIf([EVF2 Date]=1,1,0)+IIf([Medical2 date]=1,1,0)+IIf([Pic2 date]=1,1,0)+IIf([Police2 date]=1,1,0)+IIf([TT2 Recvd]=1,1,0)+IIf([Med2 Recvd]=1,1,0))

PS count is not a synonym of sum

count counts occurences of a value
Sum totals numeric values in a field

for example a field called mytotal has 21 non null entries, therfore count returns 21. If each mytotal field contained the value of 2 then the sum will be 42

 

[stopher]

As Dennisk says, you can't sum a text box. You can only sum the underlying data source. It's easy to be led into believing you are summing a text box because by default Access gives the same name to text boxes as the underlying data source.

You might want to consider performing the IIF calculation in your data source (in the query as a new field). Then you will be able to sum normally. I don't know if this is more efficient but it is certainly much easier to maintain.

Chris

 

[svgelacio]

ok, i got the idea (both ideas that is) and i wanted to count the occurences of 0, how can i do that? thats the one i wanted to reflect in my report fotter.

 

[Dennisk]

try

SELECT COUNT(MyField) as CountOfMyField FROM myTable
WHERE MyField=0