Adding up yes value in report from form

[mtc67]

I am having difficulty in figuring out how to allow my yes/no field to be added up in my report. I have used the report wizard and asked for the counting of the fields but its answer becomes "yes" and "no" instead of values.
I have checked the archives and found one posting that seems similar but i don't understand what the person means. The site of that one was
http://www.access-programmers.co.uk/ubb/Forum5/HTML/000453.html

I am very confused right now and i just want a count of records that are yes but want all records shown.

Any help at all would be appriciated.

Thanks

 

[mtc67]

i tried that and it still gives an answer of "yes" - no values just the word "yes"

here is what i typed in the sum field of my textbox of my report:
=Sum([NewMember]*-1)

i have also tried:
=Sum([NewMember])*-1

Am I doing something wrong here?

I just tried a count of the number of records and it did not seem to work either, Maybe my syntax is incorrect. It was:
=DCount("[new member]","tblProjectVolunteerInformation","[new member}=-1")

from the microsoft help file i assumed that new member was the field i wanted calculated and tblProjectVolunteerInformation was the table it refered to while the last was the calculation for the number of "yes" answers in the field. I might be going way off track here.

[This message has been edited by mtc67 (edited 06-25-2001).]

[This message has been edited by mtc67 (edited 06-25-2001).]

 

[Fornatian]

Have you tried:
=Sum(Iif([NewMember]=True,1,0))

this will count all the Yes's I think.

Ian

 

[mtc67]

it still comes back with a "yes" for an answer.

 

[mtc67]

Has anyone been able to think of why this count and sum feature is not working?

I have also made a fake table to try out different ways of getting the value into my system including a check, value list and text box. All of them bring back a "yes".

If anyone could even suggest an alternate way of creating this report so that i can count and record all new members as well as old members it would be great.

I hope someone can help and for all that try thanks.

 

[KDg]

Hi,

i would guess that the maths is being done fine as suggested above. It may just be the format of the display box - check that it is not set to "Yes/No" - that will display any non-zero result as "Yes". If it is there just remove it and it should be sorted, if not then... don't know

HTH

Drew

[This message has been edited by KDg (edited 06-26-2001).]

 

[Fornatian]

where are you storing the formula - it should be in the control source.

also make sure that the field is not bound to any field, putting the formula in the control source will do this.

if in doubt, put an unbound box on the form/report and start again.

Ian

 

[mtc67]

thanks everyone...
changing the format from a yes/no worked!
I was not aware of it being important.

Thanks to everyone who helped a struggling but stubborn student out.

 

[mtc67]

i have now another question about the same report...

I want to have a sum of only the new members hours and i want to add up the old members hours as well.

I have tried a query and it tells me that I cannot. I put my fields in and tried to get the sum of new members by also adding:
field: expression1:sum([hoursworked])
table: tblProjectVolunteers
criteria: [newmember]=0

it is probably something obvious but i can't seem to see it.

I also tried to put the following into the control source of my textbox:
sum(iif([newmember]=true,sum([hoursworked]),0))

I know what i want to ask:
if the new member field is a true/yes then take their hours and add them up but i am lost as to how to continue.

Again thanks for your time.

 

[Fornatian]

Instead of:

sum(iif([newmember]=true,sum([hoursworked]),0))

try

'will give new members hours sum
sum(iif([newmember]=true,[hoursworked],0))

'will total everything
sum([hoursworked])

ian

 

[mtc67]

Thanks a lot. The solution worked great!

Your help worked again.