Sum several fields and "IF"

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

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


"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.

"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??

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

"Al Camp" wrote:

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

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


"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.

"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??

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.

--
Wayne Morgan
MS Access MVP


"Al Camp" wrote in message
...
SJW,
Whew!! I found it... it took a while. Nothing to do with commas...

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

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


"Wayne Morgan" wrote in message
...
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.

--
Wayne Morgan
MS Access MVP


"Al Camp" wrote in message
...
SJW,
Whew!! I found it... it took a while. Nothing to do with commas...

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

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

"Al Camp" wrote:

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


"Wayne Morgan" wrote in message
...
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.

--
Wayne Morgan
MS Access MVP


"Al Camp" wrote in message
...
SJW,
Whew!! I found it... it took a while. Nothing to do with commas...

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

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.

--
Wayne Morgan
MS Access MVP


"SJW123" wrote in message
...
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.