#error msg when summing on report - please help

[dceglar1]

#Error message when adding items with a 0 or -1.

I have fields that are generated with a checkbox which results in a -1 in the underlying table if it the box is checked and a 0 if it was checked at one time and then is unchecked. However, I cleared out all entries in some of the fields - to have a clean slate for the new school year. Now, since there are no 0s or -1s my sum calculations result in an #error message on the reports. If I go and put in some 0s, it fixes it - but I would like to fix it properly with a correct calculations

Original Expression =Sum([ESY READ])*-1
(this worked great while there was something to add
(0 or -1) in the fields. I was multiplying it by *-1
to add up the -1s and then change them to a positive #.

Some of the many expressions that I have tried:
=IIf(IsNull(sum([ESY READ])),0,sum([ESY READ])*-1)
=IIf(Sum([ESY READ]) IsNull,0,Sum([ESY READ])*-1)
=IIf(IsNull Sum([ESY READ]),0,Sum([ESY READ])*-1)
=IIf(IsNull(Sum([ESY READ])),0,Sum([ESY READ])*-1)
=Sum(IIf([ESY READ]) IsNull," ",Sum([ESY READ])*-1)
=If(Sum([ESY READ])="",0,Sum([ESY READ])*-1)

I have also experimented with the Count function since I am basically counting records that have a -1 in the [ESY READ] field. Also, I have a built in query for the report that only pulls records if a -1 exist in the ESY READ field.

Any help would be greatly appreciated!

 

[lupins46]

Since you are teating nulls as zeros(=False) why don't you just set the field values to False in the first place?

 

[dceglar1]

That is interesting - I could set the field to 0 with a default value but I think that would only take care of any new records. Is there an easy way to set [ESY READ] value for all existing records to 0?

 

[dceglar1]

I have done some more experimenting. I was incorrect, the sum or the count does not work if there are 0s. There has to be at least one record with a -1. I have tried switching the field to a Yes/No field - with the same results it only works if there is at least one record with a Yes. ????

 

[lupins46]

Are you saying your Checkbox was NOT linked to a Yes/No field? That would be most unusual.

It seems to me that you can do all the totals you want by having a Yes/No field defaulting to false.

To get your starting position you just need an update query to set all the current values of the yes/no field to False.

In number terms, False =0 and True is anything other than 0 although if you let Access do the settings for you it will always be -1.

So you can use things like abs(Sum([yesnofieldname])) to get the (positive) total of Yeses.

 

[dceglar1]

My false does appear as a 0 in the underlying table. And when I tried to switch the type of the field to Yes/No, it behaved the same. So, it appears that No and false = 0. Could the problem be related to the fact that I have a built-in query that only produces records if the field equals -1 which is a Yes or a positive. Since I have removed all -1 to reset the fields for the new year the report is producing no records. Since there are no records I want the totals in the report footer to be "0"; but I am getting "#error" instead.

 

[dceglar1]

I was able to fix this by:

=IIf([HasData],Sum([ESY READ])*-1,"0")

It is probably not the prettiest but it works