Sum several fields and "IF"

[Guest]

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

 

[Al Camp]

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.

 

[Guest]

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

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

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
: 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

 

[Al Camp]

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(Score<20,"Proceed", IIF(Score=>20 and Score<50, "Proceed with
Caution",IIF(Score=>50 and Score<75,"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

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

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

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

 

[Guest]

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:

Caution",IIF(Score=>50 and Score<75,"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

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(Score<20,"Proceed", IIF(Score=>20 and Score<50, "Proceed with
Caution",IIF(Score=>50 and Score<75,"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

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

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


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

 

[Wayne Morgan]

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.

 

[Guest]

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

 

[Al Camp]

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.

 

[Guest]

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

 

[Wayne Morgan]

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?

 

[Al Camp]

SJW,
Whew!! I found it... it took a while. Nothing to do with commas...

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

The >= (correct) operators were reversed (I had =>)

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

 

[Guest]

That did it.
Many thanks Al and Wayne for your persistence and patience.
SJW

 

[Wayne Morgan]

Good catch Al. I saw that they were reversed, but it works in the immediate
window when you reverse them. Apparently the problem is just that either IIf
or Jet doesn't like it but VBA doesn't care.

 

[Al Camp]

Thanks Wayne. Good to know that...
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

 

[Guest]

Al or Wayne
If you are still reading this string, how can I calculate Score for
inclusion into a report. Also, how can I get the corresponding Text Msge from
Score (re. proceeding etc) onto the same report.
many thanks

 

[Wayne Morgan]

Doing this on a report would be identical to doing this on the form. You
would use the addition equation as the control source for one textbox and
the IIf equation for the control source of another textbox. These textboxes
would most like be in the Detail section of the report.