#NAME or #ERROR on Report 1

[xbigblue]

Hello friends,

I am using a grouping query to sum fields that are passed on to a report. This is Access 2000 with SR1-A applied. The technique in the query seems to yield OK results in the query and sometimes in the report. The summed fields are type "YES/NO" data. I have not seen any documentation that says I can't do it and it seems to work fine in test queries. What I am looking for is the count of the number of records in the control group that have YES value in 14 fields. Since YES is stored as -1, and NO as zero, I figure the arithmetic sum of a series of -1's is just the negative of the count. Problem is on report, where I use a technique to convert the negative sums to positives for display by coding in the control source:

=-1*[fieldname]

where fieldname is the summed value from the source query. I have two columns of data on report, first column represents 7 goals, second column represents the corresponding 7 goals' acheivement (and the data values are the count of the yes's from table). First column displays correctly. Second column displays inconsistenly. Some are correct, some show #error in output. I have also seen #name in some cases. If I revert to just the fieldname as the control source, it seems OK - but not consistently.
I've found that taking off the =-1* notation does not always solve the problem. One particular field must be in the control source without brackets. Putting the brackets on also yields the #error type messages. Does Access get upset if we switch a textbox from a bound control to an unbound and then change it back to unbound?

Has anybody run into this before? PS, this is a linked database environment. Queries, forms, reports are in database 1, tables are in database 2 which is a replicated database.

Will appreciate all leads.
xbigblue

 

[xbigblue]

Hello All,

Responding to my own post; if you've read this and wondered what in the world, I solved it but don't know why. I had to give up using the grouped query that was input to the report; I had to include a detail section and develop the yes/no totals in a group footer; and I had to clean up the filtering I was doing in VBA. Now the concept of summing yes/no fields AND the use of -1 times the summed values AND the use of IIF in the function to prevent divide by zero all work fine. Who knows?

xbigblue

 

[camidon]

Just for future reference you might try using the abs() function to do this.

This will return the absolute value of the number. This should be what you want.

 

[xbigblue]

Camidon,

Thanks! As an old math teacher, and soemtimes programmer in many languages, absolute value should have been the first thing I thought of. But I guess it was just lack of clear thinking. I'll file that thought away.

regards,
xbigblue