Sum several fields and "IF"

On my form I have 12 criteria (fields). Within each criteria the person must
rank that specific criteria from 1 to 10 (best to worst). At the end of the
12 criteria, I would like to be able to provide a penultimate field which
sums all 12 criteria values to provide a total score. Then, with this total
score, I want a final result to be chosen from one of four possibilities ie.
20 : proceed; 20-50 : proceed with caution; 50-75 : serious doubts; and 75
: don't proceed. The remainder of the database is working well, but it would
be more effective if we could provide an automatic tallying and advice as
above.
I hope this is enough information to provide advice.
many thanks
Steven

SJW,
To sum all the fields, create a calculated text control called TotalScore
with a ControlSource of... (use your own names)
=NZ([F1]) + NZ([F2]) + NZ([F3]).... etc... to NZ([F12])
Place a button on your form called cmdEvaluate, and on the OnClick event,
use a SelectCase statement to evaluate the TotalScore value, and post the
appropriate textual message to the user.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


"SJW123" wrote in message
...
On my form I have 12 criteria (fields). Within each criteria the person
must
rank that specific criteria from 1 to 10 (best to worst). At the end of
the
12 criteria, I would like to be able to provide a penultimate field which
sums all 12 criteria values to provide a total score. Then, with this
total
score, I want a final result to be chosen from one of four possibilities
ie.
20 : proceed; 20-50 : proceed with caution; 50-75 : serious doubts; and
75
: don't proceed. The remainder of the database is working well, but it
would
be more effective if we could provide an automatic tallying and advice as
above.
I hope this is enough information to provide advice.
many thanks
Steven

Thanks Al.
Can you pls provide more details. I have a form based on tblProject and a
field from tblProject called Score. The 12 criteria fields are in tblProject.
I presume a calc txt control is a text box? Therefore, at the control source
of the txtbox, which I have named 'score' I have
=Nz([tblProject]![Criteria1]) + Nz([tblProject]![Criteria2])
......................Nz([tblProject]![Criteria12]) . But this is not correct
as the #Name? error appears in the Score txt box. Or should I be using a
field named 'score' on tblProject?
Grateful any further advice broken down for a beginner.
many thanks
sjw

"Al Camp" wrote:

SJW,
To sum all the fields, create a calculated text control called TotalScore
with a ControlSource of... (use your own names)
=NZ([F1]) + NZ([F2]) + NZ([F3]).... etc... to NZ([F12])
Place a button on your form called cmdEvaluate, and on the OnClick event,
use a SelectCase statement to evaluate the TotalScore value, and post the
appropriate textual message to the user.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


"SJW123" wrote in message
...
On my form I have 12 criteria (fields). Within each criteria the person
must
rank that specific criteria from 1 to 10 (best to worst). At the end of
the
12 criteria, I would like to be able to provide a penultimate field which
sums all 12 criteria values to provide a total score. Then, with this
total
score, I want a final result to be chosen from one of four possibilities
ie.
20 : proceed; 20-50 : proceed with caution; 50-75 : serious doubts; and
75
: don't proceed. The remainder of the database is working well, but it
would
be more effective if we could provide an automatic tallying and advice as
above.
I hope this is enough information to provide advice.
many thanks
Steven

SJW,
Aren't these Criteria fields on a form already? The table that holds
these Criteria fields should be in the RecordSource for your form... either
as a table, or a query.
If you place a text control on the form with a ControlSource of
[Criteria1], does it display or accept a value without a #Name error? How
about [Criteria2]... etc.. etc...

When all 12 values are on the form, and have been given values, then a
Text Control with a calculated ControlSource of
=NZ([Criteria1]) + Nz([Criteria2]) + etc........ + NZ([Criteria12])
should yield the [Score].

You should not have a [Score] field in your table! Score is a calculated
field only, and is not saved. Since you have Criteria 1-12 values stored,
you can always recalculate [Score] in any subsequent form, query, or report.

If you are unfamiliar with how to use control events to run VB code in
the form module, then try this instead...
Create another "unbound" calculated field with this for a
ControlSource... (all one line)
=IIF(Score20,"Proceed", IIF(Score=20 and Score50, "Proceed with
Caution",IIF(Score=50 and Score75,"Serious Doubts", "Don't Proceed")))

This method should work (I couldn't test), but it is a bit cumbersome.
The best way is to use the OnClick event of a button (say... Evaluate), that
would run an Event Procedure that utilizes a Select Case to make the Score
evaluation. Check Help on Event Procedures and Select Case.

--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


"SJW123" wrote in message
...
Thanks Al.
Can you pls provide more details. I have a form based on tblProject and a
field from tblProject called Score. The 12 criteria fields are in
tblProject.
I presume a calc txt control is a text box? Therefore, at the control
source
of the txtbox, which I have named 'score' I have
=Nz([tblProject]![Criteria1]) + Nz([tblProject]![Criteria2])
.....................Nz([tblProject]![Criteria12]) . But this is not
correct
as the #Name? error appears in the Score txt box. Or should I be using a
field named 'score' on tblProject?
Grateful any further advice broken down for a beginner.
many thanks
sjw

"Al Camp" wrote:

SJW,
To sum all the fields, create a calculated text control called
TotalScore
with a ControlSource of... (use your own names)
=NZ([F1]) + NZ([F2]) + NZ([F3]).... etc... to NZ([F12])
Place a button on your form called cmdEvaluate, and on the OnClick
event,
use a SelectCase statement to evaluate the TotalScore value, and post the
appropriate textual message to the user.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


"SJW123" wrote in message
...
On my form I have 12 criteria (fields). Within each criteria the person
must
rank that specific criteria from 1 to 10 (best to worst). At the end of
the
12 criteria, I would like to be able to provide a penultimate field
which
sums all 12 criteria values to provide a total score. Then, with this
total
score, I want a final result to be chosen from one of four
possibilities
ie.
20 : proceed; 20-50 : proceed with caution; 50-75 : serious doubts;
and
75
: don't proceed. The remainder of the database is working well, but it
would
be more effective if we could provide an automatic tallying and advice
as
above.
I hope this is enough information to provide advice.
many thanks
Steven

Thanks again Al.
Have managed to get the field score working well.
For the second part, I decided to go with your second recommendation ie. the
unbound calc. field. Are you sure this is the right source code:
=IIF(Score20,"Proceed", IIF(Score=20 and Score50, "Proceed with
Caution",IIF(Score=50 and Score75,"Serious Doubts", "Don't Proceed"))).
I cannot get this code to remain in the control source. There is an syntax
error message saying "entered a comma without a preceding value or
identifier". I have tried taking spaces out etc. but nothing works. Also, do
I need a statement for score=75??
thanks sjw

"Al Camp" wrote:

SJW,
Aren't these Criteria fields on a form already? The table that holds
these Criteria fields should be in the RecordSource for your form... either
as a table, or a query.
If you place a text control on the form with a ControlSource of
[Criteria1], does it display or accept a value without a #Name error? How
about [Criteria2]... etc.. etc...

When all 12 values are on the form, and have been given values, then a
Text Control with a calculated ControlSource of
=NZ([Criteria1]) + Nz([Criteria2]) + etc........ + NZ([Criteria12])
should yield the [Score].

You should not have a [Score] field in your table! Score is a calculated
field only, and is not saved. Since you have Criteria 1-12 values stored,
you can always recalculate [Score] in any subsequent form, query, or report.

If you are unfamiliar with how to use control events to run VB code in
the form module, then try this instead...
Create another "unbound" calculated field with this for a
ControlSource... (all one line)
=IIF(Score20,"Proceed", IIF(Score=20 and Score50, "Proceed with
Caution",IIF(Score=50 and Score75,"Serious Doubts", "Don't Proceed")))

This method should work (I couldn't test), but it is a bit cumbersome.
The best way is to use the OnClick event of a button (say... Evaluate), that
would run an Event Procedure that utilizes a Select Case to make the Score
evaluation. Check Help on Event Procedures and Select Case.

--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


"SJW123" wrote in message
...
Thanks Al.
Can you pls provide more details. I have a form based on tblProject and a
field from tblProject called Score. The 12 criteria fields are in
tblProject.
I presume a calc txt control is a text box? Therefore, at the control
source
of the txtbox, which I have named 'score' I have
=Nz([tblProject]![Criteria1]) + Nz([tblProject]![Criteria2])
.....................Nz([tblProject]![Criteria12]) . But this is not
correct
as the #Name? error appears in the Score txt box. Or should I be using a
field named 'score' on tblProject?
Grateful any further advice broken down for a beginner.
many thanks
sjw

"Al Camp" wrote:

SJW,
To sum all the fields, create a calculated text control called
TotalScore
with a ControlSource of... (use your own names)
=NZ([F1]) + NZ([F2]) + NZ([F3]).... etc... to NZ([F12])
Place a button on your form called cmdEvaluate, and on the OnClick
event,
use a SelectCase statement to evaluate the TotalScore value, and post the
appropriate textual message to the user.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


"SJW123" wrote in message
...
On my form I have 12 criteria (fields). Within each criteria the person
must
rank that specific criteria from 1 to 10 (best to worst). At the end of
the
12 criteria, I would like to be able to provide a penultimate field
which
sums all 12 criteria values to provide a total score. Then, with this
total
score, I want a final result to be chosen from one of four
possibilities
ie.
20 : proceed; 20-50 : proceed with caution; 50-75 : serious doubts;
and
75
: don't proceed. The remainder of the database is working well, but it
would
be more effective if we could provide an automatic tallying and advice
as
above.
I hope this is enough information to provide advice.
many thanks
Steven

PMJI,

The control source listed by Al appears to be correct. However, the
newsreader has wrapped it, it should all be on one line. No, you don't need
a =75 option. If the other options fail, the equation will return "Don't
Proceed" as the only option left. Adding = 75 would prevent a response to a
Null value, but the Nz function in the first equation where you're adding up
the fields has already taken care of the Null, so it shouldn't be a problem.

--
Wayne Morgan
MS Access MVP


"SJW123" wrote in message
...
Thanks again Al.
Have managed to get the field score working well.
For the second part, I decided to go with your second recommendation ie.
the
unbound calc. field. Are you sure this is the right source code:
=IIF(Score20,"Proceed", IIF(Score=20 and Score50, "Proceed with
Caution",IIF(Score=50 and Score75,"Serious Doubts", "Don't Proceed"))).
I cannot get this code to remain in the control source. There is an syntax
error message saying "entered a comma without a preceding value or
identifier". I have tried taking spaces out etc. but nothing works. Also,
do
I need a statement for score=75??

Wayne or Al,
Could you pls check the code again.
I have tried everything to try and get it to work but cannot - continually
get the syntax error message about "entered a comma without a preceding value
or identifier".
many thanks
SJW

"Wayne Morgan" wrote:

PMJI,

The control source listed by Al appears to be correct. However, the
newsreader has wrapped it, it should all be on one line. No, you don't need
a =75 option. If the other options fail, the equation will return "Don't
Proceed" as the only option left. Adding = 75 would prevent a response to a
Null value, but the Nz function in the first equation where you're adding up
the fields has already taken care of the Null, so it shouldn't be a problem.

--
Wayne Morgan
MS Access MVP


"SJW123" wrote in message
...
Thanks again Al.
Have managed to get the field score working well.
For the second part, I decided to go with your second recommendation ie.
the
unbound calc. field. Are you sure this is the right source code:
=IIF(Score20,"Proceed", IIF(Score=20 and Score50, "Proceed with
Caution",IIF(Score=50 and Score75,"Serious Doubts", "Don't Proceed"))).
I cannot get this code to remain in the control source. There is an syntax
error message saying "entered a comma without a preceding value or
identifier". I have tried taking spaces out etc. but nothing works. Also,
do
I need a statement for score=75??

No... the syntax should be OK. All commas are where they should be.
So...
"Copy and paste" your calculation "exactly" into a post, and send. Do not
copy it into an email by hand... use copy and paste.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

"SJW123" wrote in message
...
Wayne or Al,
Could you pls check the code again.
I have tried everything to try and get it to work but cannot - continually
get the syntax error message about "entered a comma without a preceding
value
or identifier".
many thanks
SJW

"Wayne Morgan" wrote:

PMJI,

The control source listed by Al appears to be correct. However, the
newsreader has wrapped it, it should all be on one line. No, you don't
need
a =75 option. If the other options fail, the equation will return "Don't
Proceed" as the only option left. Adding = 75 would prevent a response
to a
Null value, but the Nz function in the first equation where you're adding
up
the fields has already taken care of the Null, so it shouldn't be a
problem.

--
Wayne Morgan
MS Access MVP


"SJW123" wrote in message
...
Thanks again Al.
Have managed to get the field score working well.
For the second part, I decided to go with your second recommendation
ie.
the
unbound calc. field. Are you sure this is the right source code:
=IIF(Score20,"Proceed", IIF(Score=20 and Score50, "Proceed with
Caution",IIF(Score=50 and Score75,"Serious Doubts", "Don't
Proceed"))).
I cannot get this code to remain in the control source. There is an
syntax
error message saying "entered a comma without a preceding value or
identifier". I have tried taking spaces out etc. but nothing works.
Also,
do
I need a statement for score=75??

This is the code I am trying to insert.
=IIF(Score20,"Proceed", IIF(Score=20 and Score50,"Proceed with
Caution",IIF(Score=50 and Score75,"Serious Doubts", "Don't Proceed")))
But I cannot get it to stay in the control source of the txt box.

"Al Camp" wrote:

No... the syntax should be OK. All commas are where they should be.
So...
"Copy and paste" your calculation "exactly" into a post, and send. Do not
copy it into an email by hand... use copy and paste.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

"SJW123" wrote in message
...
Wayne or Al,
Could you pls check the code again.
I have tried everything to try and get it to work but cannot - continually
get the syntax error message about "entered a comma without a preceding
value
or identifier".
many thanks
SJW

"Wayne Morgan" wrote:

PMJI,

The control source listed by Al appears to be correct. However, the
newsreader has wrapped it, it should all be on one line. No, you don't
need
a =75 option. If the other options fail, the equation will return "Don't
Proceed" as the only option left. Adding = 75 would prevent a response
to a
Null value, but the Nz function in the first equation where you're adding
up
the fields has already taken care of the Null, so it shouldn't be a
problem.

--
Wayne Morgan
MS Access MVP


"SJW123" wrote in message
...
Thanks again Al.
Have managed to get the field score working well.
For the second part, I decided to go with your second recommendation
ie.
the
unbound calc. field. Are you sure this is the right source code:
=IIF(Score20,"Proceed", IIF(Score=20 and Score50, "Proceed with
Caution",IIF(Score=50 and Score75,"Serious Doubts", "Don't
Proceed"))).
I cannot get this code to remain in the control source. There is an
syntax
error message saying "entered a comma without a preceding value or
identifier". I have tried taking spaces out etc. but nothing works.
Also,
do
I need a statement for score=75??

Try placing brackets around Score.

=IIF([Score]20,"Proceed", IIF([Score]=20 and [Score]50,"Proceed with
Caution",IIF([Score]=50 and [Score]75,"Serious Doubts", "Don't Proceed")))

Also, is Score a number field or a text data type that has numbers in it? If
it is text that has numbers in it, do you have commas in the numbers?

--
Wayne Morgan
MS Access MVP


"SJW123" wrote in message
...
This is the code I am trying to insert.
=IIF(Score20,"Proceed", IIF(Score=20 and Score50,"Proceed with
Caution",IIF(Score=50 and Score75,"Serious Doubts", "Don't Proceed")))
But I cannot get it to stay in the control source of the txt box.