If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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?? |
#12
|
|||
|
|||
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?? |
#13
|
|||
|
|||
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 |
#14
|
|||
|
|||
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 |
#15
|
|||
|
|||
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 |
#16
|
|||
|
|||
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. |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
improving performance by indexing query criteria fields | Paul James | General Discussion | 20 | February 16th, 2005 07:55 PM |
improving performance by indexing query criteria fields | Paul James | Running & Setting Up Queries | 20 | February 16th, 2005 07:55 PM |
adding 2 fields including null entries | Jesse | Running & Setting Up Queries | 26 | January 18th, 2005 05:31 PM |
Can't Add Fields to Form | Jeff Miller | Using Forms | 4 | January 12th, 2005 03:42 AM |
Automatic filling of fields in table two from table one | Jim Kelly | Database Design | 1 | September 27th, 2004 10:16 PM |