YES/NO calculations

[Cruisinid]

I need to be able to calculate the number of "yes" values that appear on the
form. How do I do that? (I currently have a hidden box on the form to store
the information.. do I need to do a query and then insert the query info
there? And if so, how would I write the query?)

Thanks in advance,
Dawn

 

[Keith Wilby]

I need to be able to calculate the number of "yes" values that appear on
the
form. How do I do that? (I currently have a hidden box on the form to
store
the information.. do I need to do a query and then insert the query info
there? And if so, how would I write the query?)

Thanks in advance,
Dawn

Use the DCount function:

DCount("MyField","MyTable","MyFfield = -1")

Generally, you would not normally store a calculation.

Keith.
www.keithwilby.com

 

[fredg]

I need to be able to calculate the number of "yes" values that appear on the
form. How do I do that? (I currently have a hidden box on the form to store
the information.. do I need to do a query and then insert the query info
there? And if so, how would I write the query?)

Thanks in advance,
Dawn

"That appear on the form"?
Do you mean the total number of Yes's for a number of check box
fields displayed for one record on a form in single view?

Do you mean the total number of Yes values for more than one check box
field in the entire table which are shown on the form as you navigate
through the records?

Do you mean a number of text or combo box controls that have the text
value of "Yes"?

Do you mean the total number of text "Yes" values in the whole table?

Do you mean the total number of Yes in a single check box field?

I think you need to be more specific if you wish a specific answer.
Actual Field names and an example would be helpful.

 

[BruceM]

Do you mean within one record, or for a particular field in a range of
records? Are you referring to a Yes/No field, or text values?

Assuming you mean a Yes/No field, and that you are looking to total Yes
responses for the form's recordset you could put the following into an
unbound text box in the form footer:

=Sum(Abs([YourField]))

The Abs function returns the absolute value of a number. True is stored
as -1, and False as 0. Abs(-1) = 1, and Abs(0) = 0. The Sum function adds
these values, which has the effect of counting the records in which
YourField is True. Use your actual field name, of course.

If you are trying to count fields within a record:
=Abs([Field1] + Abs([Field2]) + Abs([Field3]) etc.

 

[Cruisinid]

I have the form set up so that when someone has completed a task they place a
check mark in the box beside the item. (ie. I was planning on completing the
vacuuming, dusting and dishes but only did the dishes= 1 out of a possible 3,
right?).

The check marks are to be tallied up on the form (1 page). The user will
not know that the checks are being counted because the total box is not
visible.

If I understand your response, inside the "total" box in the properties area
I am to build something that says =sum([dishes]+[vacuuming]+[dusting]) and
because I only checked off the dishes it would show a value of one? Could it
be that easy?

Do you mean within one record, or for a particular field in a range of
records? Are you referring to a Yes/No field, or text values?

Assuming you mean a Yes/No field, and that you are looking to total Yes
responses for the form's recordset you could put the following into an
unbound text box in the form footer:

=Sum(Abs([YourField]))

The Abs function returns the absolute value of a number. True is stored
as -1, and False as 0. Abs(-1) = 1, and Abs(0) = 0. The Sum function adds
these values, which has the effect of counting the records in which
YourField is True. Use your actual field name, of course.

If you are trying to count fields within a record:
=Abs([Field1] + Abs([Field2]) + Abs([Field3]) etc.

I need to be able to calculate the number of "yes" values that appear on
the
form. How do I do that? (I currently have a hidden box on the form to
store
the information.. do I need to do a query and then insert the query info
there? And if so, how would I write the query?)

Thanks in advance,
Dawn

 

[fredg]

I have the form set up so that when someone has completed a task they place a
check mark in the box beside the item. (ie. I was planning on completing the
vacuuming, dusting and dishes but only did the dishes= 1 out of a possible 3,
right?).

The check marks are to be tallied up on the form (1 page). The user will
not know that the checks are being counted because the total box is not
visible.

If I understand your response, inside the "total" box in the properties area
I am to build something that says =sum([dishes]+[vacuuming]+[dusting]) and
because I only checked off the dishes it would show a value of one? Could it
be that easy?

Do you mean within one record, or for a particular field in a range of
records? Are you referring to a Yes/No field, or text values?

Assuming you mean a Yes/No field, and that you are looking to total Yes
responses for the form's recordset you could put the following into an
unbound text box in the form footer:

=Sum(Abs([YourField]))

The Abs function returns the absolute value of a number. True is stored
as -1, and False as 0. Abs(-1) = 1, and Abs(0) = 0. The Sum function adds
these values, which has the effect of counting the records in which
YourField is True. Use your actual field name, of course.

If you are trying to count fields within a record:
=Abs([Field1] + Abs([Field2]) + Abs([Field3]) etc.

I need to be able to calculate the number of "yes" values that appear on
the
form. How do I do that? (I currently have a hidden box on the form to
store
the information.. do I need to do a query and then insert the query info
there? And if so, how would I write the query?)

Thanks in advance,
Dawn

Almost that easy. A check box has a value of either -1 or 0.
Summing them would result in a negative number, i.e. -1 + -1 + 0 = -2.
So.. Use the Abs function to return the positive value:
=Abs(Sum([CheckA]+[CheckB] + [CheckC]))
This will return the total sum of all 3 check fields for ALL records.
Is that what you want?
Or do you want only the sum of the check marks for this one individual
record?
In which case you don't use Sum, you just add them.
=Abs([CheckA] + [CheckB] + [CheckC])

 

[Cruisinid]

Excellent, thank you. Yes, I just need the total for each individual record
so I will use the second formula. In terms of where I place the formula, I
enter the formula in the box that I'm using for the total, right? (I build an
expression using the information in that box?)

I have the form set up so that when someone has completed a task they place a
check mark in the box beside the item. (ie. I was planning on completing the
vacuuming, dusting and dishes but only did the dishes= 1 out of a possible 3,
right?).

The check marks are to be tallied up on the form (1 page). The user will
not know that the checks are being counted because the total box is not
visible.

If I understand your response, inside the "total" box in the properties area
I am to build something that says =sum([dishes]+[vacuuming]+[dusting]) and
because I only checked off the dishes it would show a value of one? Could it
be that easy?

Do you mean within one record, or for a particular field in a range of
records? Are you referring to a Yes/No field, or text values?

Assuming you mean a Yes/No field, and that you are looking to total Yes
responses for the form's recordset you could put the following into an
unbound text box in the form footer:

=Sum(Abs([YourField]))

The Abs function returns the absolute value of a number. True is stored
as -1, and False as 0. Abs(-1) = 1, and Abs(0) = 0. The Sum function adds
these values, which has the effect of counting the records in which
YourField is True. Use your actual field name, of course.

If you are trying to count fields within a record:
=Abs([Field1] + Abs([Field2]) + Abs([Field3]) etc.

I need to be able to calculate the number of "yes" values that appear on
the
form. How do I do that? (I currently have a hidden box on the form to
store
the information.. do I need to do a query and then insert the query info
there? And if so, how would I write the query?)

Thanks in advance,
Dawn

Almost that easy. A check box has a value of either -1 or 0.
Summing them would result in a negative number, i.e. -1 + -1 + 0 = -2.
So.. Use the Abs function to return the positive value:
=Abs(Sum([CheckA]+[CheckB] + [CheckC]))
This will return the total sum of all 3 check fields for ALL records.
Is that what you want?
Or do you want only the sum of the check marks for this one individual
record?
In which case you don't use Sum, you just add them.
=Abs([CheckA] + [CheckB] + [CheckC])

 

[Cruisinid]

I entered the following into the box using the builder:

=abs[1completed]+[2completed]+[3completed] --those are the acutal names
I gave the checks so that I was comparing the same account number. After
check are made though, the total isn't being tallied. Should I have this "on
click" "on exit" or something special?

I have the form set up so that when someone has completed a task they place a
check mark in the box beside the item. (ie. I was planning on completing the
vacuuming, dusting and dishes but only did the dishes= 1 out of a possible 3,
right?).

The check marks are to be tallied up on the form (1 page). The user will
not know that the checks are being counted because the total box is not
visible.

If I understand your response, inside the "total" box in the properties area
I am to build something that says =sum([dishes]+[vacuuming]+[dusting]) and
because I only checked off the dishes it would show a value of one? Could it
be that easy?

Do you mean within one record, or for a particular field in a range of
records? Are you referring to a Yes/No field, or text values?

Assuming you mean a Yes/No field, and that you are looking to total Yes
responses for the form's recordset you could put the following into an
unbound text box in the form footer:

=Sum(Abs([YourField]))

The Abs function returns the absolute value of a number. True is stored
as -1, and False as 0. Abs(-1) = 1, and Abs(0) = 0. The Sum function adds
these values, which has the effect of counting the records in which
YourField is True. Use your actual field name, of course.

If you are trying to count fields within a record:
=Abs([Field1] + Abs([Field2]) + Abs([Field3]) etc.

I need to be able to calculate the number of "yes" values that appear on
the
form. How do I do that? (I currently have a hidden box on the form to
store
the information.. do I need to do a query and then insert the query info
there? And if so, how would I write the query?)

Thanks in advance,
Dawn

Almost that easy. A check box has a value of either -1 or 0.
Summing them would result in a negative number, i.e. -1 + -1 + 0 = -2.
So.. Use the Abs function to return the positive value:
=Abs(Sum([CheckA]+[CheckB] + [CheckC]))
This will return the total sum of all 3 check fields for ALL records.
Is that what you want?
Or do you want only the sum of the check marks for this one individual
record?
In which case you don't use Sum, you just add them.
=Abs([CheckA] + [CheckB] + [CheckC])

 

[fredg]

I entered the following into the box using the builder:

=abs[1completed]+[2completed]+[3completed] --those are the acutal names
I gave the checks so that I was comparing the same account number. After
check are made though, the total isn't being tallied. Should I have this "on
click" "on exit" or something special?

I have the form set up so that when someone has completed a task they place a
check mark in the box beside the item. (ie. I was planning on completing the
vacuuming, dusting and dishes but only did the dishes= 1 out of a possible 3,
right?).

The check marks are to be tallied up on the form (1 page). The user will
not know that the checks are being counted because the total box is not
visible.

If I understand your response, inside the "total" box in the properties area
I am to build something that says =sum([dishes]+[vacuuming]+[dusting]) and
because I only checked off the dishes it would show a value of one? Could it
be that easy?

:

Do you mean within one record, or for a particular field in a range of
records? Are you referring to a Yes/No field, or text values?

Assuming you mean a Yes/No field, and that you are looking to total Yes
responses for the form's recordset you could put the following into an
unbound text box in the form footer:

=Sum(Abs([YourField]))

The Abs function returns the absolute value of a number. True is stored
as -1, and False as 0. Abs(-1) = 1, and Abs(0) = 0. The Sum function adds
these values, which has the effect of counting the records in which
YourField is True. Use your actual field name, of course.

If you are trying to count fields within a record:
=Abs([Field1] + Abs([Field2]) + Abs([Field3]) etc.

I need to be able to calculate the number of "yes" values that appear on
the
form. How do I do that? (I currently have a hidden box on the form to
store
the information.. do I need to do a query and then insert the query info
there? And if so, how would I write the query?)

Thanks in advance,
Dawn

Almost that easy. A check box has a value of either -1 or 0.
Summing them would result in a negative number, i.e. -1 + -1 + 0 = -2.
So.. Use the Abs function to return the positive value:
=Abs(Sum([CheckA]+[CheckB] + [CheckC]))
This will return the total sum of all 3 check fields for ALL records.
Is that what you want?
Or do you want only the sum of the check marks for this one individual
record?
In which case you don't use Sum, you just add them.
=Abs([CheckA] + [CheckB] + [CheckC])

No, you place it directly on the control source property line of the
unbound control.
Problem is though, you didn't write the expression correctly.
You left out the very important parenthesis.

=abs([1completed]+[2completed]+[3completed])

 

[Cruisinid]

Fred you are so patient!!!

Sorry, I did have the brackets in, as a matter of fact I copied and pasted
your example Now, when I open the form there is an error ?Name grrrr..
WHat does that mean?

Also, will this tally things as they are being added or just on exit? Just
wondering if I wouldn't see anything yet because I'm not supposed to?

Cheers,
D

I entered the following into the box using the builder:

=abs[1completed]+[2completed]+[3completed] --those are the acutal names
I gave the checks so that I was comparing the same account number. After
check are made though, the total isn't being tallied. Should I have this "on
click" "on exit" or something special?

On Tue, 9 Sep 2008 14:07:01 -0700, Cruisinid wrote:

I have the form set up so that when someone has completed a task they place a
check mark in the box beside the item. (ie. I was planning on completing the
vacuuming, dusting and dishes but only did the dishes= 1 out of a possible 3,
right?).

The check marks are to be tallied up on the form (1 page). The user will
not know that the checks are being counted because the total box is not
visible.

If I understand your response, inside the "total" box in the properties area
I am to build something that says =sum([dishes]+[vacuuming]+[dusting]) and
because I only checked off the dishes it would show a value of one? Could it
be that easy?

:

Do you mean within one record, or for a particular field in a range of
records? Are you referring to a Yes/No field, or text values?

Assuming you mean a Yes/No field, and that you are looking to total Yes
responses for the form's recordset you could put the following into an
unbound text box in the form footer:

=Sum(Abs([YourField]))

The Abs function returns the absolute value of a number. True is stored
as -1, and False as 0. Abs(-1) = 1, and Abs(0) = 0. The Sum function adds
these values, which has the effect of counting the records in which
YourField is True. Use your actual field name, of course.

If you are trying to count fields within a record:
=Abs([Field1] + Abs([Field2]) + Abs([Field3]) etc.

I need to be able to calculate the number of "yes" values that appear on
the
form. How do I do that? (I currently have a hidden box on the form to
store
the information.. do I need to do a query and then insert the query info
there? And if so, how would I write the query?)

Thanks in advance,
Dawn



Almost that easy. A check box has a value of either -1 or 0.
Summing them would result in a negative number, i.e. -1 + -1 + 0 = -2.
So.. Use the Abs function to return the positive value:
=Abs(Sum([CheckA]+[CheckB] + [CheckC]))
This will return the total sum of all 3 check fields for ALL records.
Is that what you want?
Or do you want only the sum of the check marks for this one individual
record?
In which case you don't use Sum, you just add them.
=Abs([CheckA] + [CheckB] + [CheckC])

No, you place it directly on the control source property line of the
unbound control.
Problem is though, you didn't write the expression correctly.
You left out the very important parenthesis.

=abs([1completed]+[2completed]+[3completed])

 

[BruceM]

There are square brackets that surround field names, and round parentheses.
Fred's example showed both. If you copied and pasted it you somehow managed
to leave the round ones behind.

This is the Control Source of an unbound text box:
=Abs ( [1completed] + [2completed] + [3completed] )

I have added some spaces in an effort to make it clearer. Note carefully
that Abs is followed by a parentheses, *then* a square bracket. A
parentheses closes the expression.

If there is an error, you need to state what it is. "An error" is very
generic, and usually cannot lead to more than a guess by a responder.

Fred you are so patient!!!

Sorry, I did have the brackets in, as a matter of fact I copied and pasted
your example Now, when I open the form there is an error ?Name
grrrr..
WHat does that mean?

Also, will this tally things as they are being added or just on exit?
Just
wondering if I wouldn't see anything yet because I'm not supposed to?

Cheers,
D

I entered the following into the box using the builder:

=abs[1completed]+[2completed]+[3completed] --those are the acutal
names
I gave the checks so that I was comparing the same account number.
After
check are made though, the total isn't being tallied. Should I have
this "on
click" "on exit" or something special?

:

On Tue, 9 Sep 2008 14:07:01 -0700, Cruisinid wrote:

I have the form set up so that when someone has completed a task they
place a
check mark in the box beside the item. (ie. I was planning on
completing the
vacuuming, dusting and dishes but only did the dishes= 1 out of a
possible 3,
right?).

The check marks are to be tallied up on the form (1 page). The user
will
not know that the checks are being counted because the total box is
not
visible.

If I understand your response, inside the "total" box in the
properties area
I am to build something that says
=sum([dishes]+[vacuuming]+[dusting]) and
because I only checked off the dishes it would show a value of one?
Could it
be that easy?

:

Do you mean within one record, or for a particular field in a range
of
records? Are you referring to a Yes/No field, or text values?

Assuming you mean a Yes/No field, and that you are looking to total
Yes
responses for the form's recordset you could put the following into
an
unbound text box in the form footer:

=Sum(Abs([YourField]))

The Abs function returns the absolute value of a number. True is
stored
as -1, and False as 0. Abs(-1) = 1, and Abs(0) = 0. The Sum
function adds
these values, which has the effect of counting the records in which
YourField is True. Use your actual field name, of course.

If you are trying to count fields within a record:
=Abs([Field1] + Abs([Field2]) + Abs([Field3]) etc.

I need to be able to calculate the number of "yes" values that
appear on
the
form. How do I do that? (I currently have a hidden box on the
form to
store
the information.. do I need to do a query and then insert the query
info
there? And if so, how would I write the query?)

Thanks in advance,
Dawn



Almost that easy. A check box has a value of either -1 or 0.
Summing them would result in a negative number, i.e. -1 + -1 + 0 = -2.
So.. Use the Abs function to return the positive value:
=Abs(Sum([CheckA]+[CheckB] + [CheckC]))
This will return the total sum of all 3 check fields for ALL records.
Is that what you want?
Or do you want only the sum of the check marks for this one individual
record?
In which case you don't use Sum, you just add them.
=Abs([CheckA] + [CheckB] + [CheckC])

No, you place it directly on the control source property line of the
unbound control.
Problem is though, you didn't write the expression correctly.
You left out the very important parenthesis.

=abs([1completed]+[2completed]+[3completed])