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 |
#1
|
|||
|
|||
IIF Statement Help
I’m looking to write an IIF statement to use as the control source for the
ATTDATE2 field. I have a form called GeneralForm with a subform on it called HistoryForm. My goal is to have: =IIF([SIGNWORK]=”Install” OR “Replace” OR “Relocate & Replace” AND [FACETYPE]=”High Intensity” OR “Diam. Grade”,[COMPDATE]+7305) Signwork and Facetype are both combo boxes. I’ve tried nesting IIF and it will work (see below), but when I try a third string, I get an error message about the wrong number of arguments. Plus, it’s complex and I’m sure there’s a better way to do this. Tried and works like this only: =IIf([SIGNWORK]="INSTALL" And [FACETYPE]="DIAM. GRADE",[COMPDATE]+7305, IIf([SIGNWORK]="REPLACE" And [FACETYPE]="HIGH INTENSITY",[COMPDATE]+7305)) Any suggestions would be appreciated. Thanks! Cathy |
#2
|
|||
|
|||
IIF Statement Help
"cathyt" wrote in message
... I’m looking to write an IIF statement to use as the control source for the ATTDATE2 field. I have a form called GeneralForm with a subform on it called HistoryForm. My goal is to have: =IIF([SIGNWORK]=”Install” OR “Replace” OR “Relocate & Replace” AND [FACETYPE]=”High Intensity” OR “Diam. Grade”,[COMPDATE]+7305) Signwork and Facetype are both combo boxes. I’ve tried nesting IIF and it will work (see below), but when I try a third string, I get an error message about the wrong number of arguments. Plus, it’s complex and I’m sure there’s a better way to do this. Tried and works like this only: =IIf([SIGNWORK]="INSTALL" And [FACETYPE]="DIAM. GRADE",[COMPDATE]+7305, IIf([SIGNWORK]="REPLACE" And [FACETYPE]="HIGH INTENSITY",[COMPDATE]+7305)) You can put multiple conditions in parentheses to group them, but you do have to repeat the comparands each time: =IIF((([SIGNWORK]=”Install”) OR ([SIGNWORK]=“Replace”) OR ([SIGNWORK]=“Relocate & Replace”)) AND (([FACETYPE]=”High Intensity”) OR ([FACETYPE]=”Diam. Grade”)), [COMPDATE]+7305) -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup) |
#3
|
|||
|
|||
IIF Statement Help
On 15 apr, 17:06, cathyt wrote:
Im looking to write an IIF statement to use as the control source for the ATTDATE2 field. *I have a form called GeneralForm with a subform on it called HistoryForm. My goal is to have: =IIF([SIGNWORK]=Install OR Replace OR Relocate & Replace AND [FACETYPE]=High Intensity OR Diam. Grade,[COMPDATE]+7305) Signwork and Facetype are both combo boxes. *Ive tried nesting IIF and it will work (see below), but when I try a third string, I get an error message about the wrong number of arguments. *Plus, its complex and Im sure theres a better way to do this. * Tried and works like this only: =IIf([SIGNWORK]="INSTALL" And [FACETYPE]="DIAM. GRADE",[COMPDATE]+7305, IIf([SIGNWORK]="REPLACE" And [FACETYPE]="HIGH INTENSITY",[COMPDATE]+7305)) Any suggestions would be appreciated. Thanks! Cathy Try: =IIF(([SIGNWORK]=Install OR [SIGNWORK]=Replace OR [SIGNWORK]=Relocate & Replace) AND ([FACETYPE]=High Intensity OR [FACETYPE]=Diam. Grade),[COMPDATE]+7305) Groeten, Peter http://access.xps350.com |
#4
|
|||
|
|||
IIF Statement Help
Peter and Dirk,
Thanks for your replies. I've tried both suggestions and for each get an error message: Expressions contains Invalid syntax; may have entered an operand without an operator. Cathy "cathyt" wrote: I’m looking to write an IIF statement to use as the control source for the ATTDATE2 field. I have a form called GeneralForm with a subform on it called HistoryForm. My goal is to have: =IIF([SIGNWORK]=”Install” OR “Replace” OR “Relocate & Replace” AND [FACETYPE]=”High Intensity” OR “Diam. Grade”,[COMPDATE]+7305) Signwork and Facetype are both combo boxes. I’ve tried nesting IIF and it will work (see below), but when I try a third string, I get an error message about the wrong number of arguments. Plus, it’s complex and I’m sure there’s a better way to do this. Tried and works like this only: =IIf([SIGNWORK]="INSTALL" And [FACETYPE]="DIAM. GRADE",[COMPDATE]+7305, IIf([SIGNWORK]="REPLACE" And [FACETYPE]="HIGH INTENSITY",[COMPDATE]+7305)) Any suggestions would be appreciated. Thanks! Cathy |
#5
|
|||
|
|||
IIF Statement Help
"cathyt" wrote in message
... Peter and Dirk, Thanks for your replies. I've tried both suggestions and for each get an error message: Expressions contains Invalid syntax; may have entered an operand without an operator. It's always possible I made a mistake in my expression, but I'm not seeing it. You mentioned that [SIGNWORK] and [FACETYPE] are combo boxes. What are the values of these properties for each: Row Source Bound Column Column Count Column Widths -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup) |
#6
|
|||
|
|||
IIF Statement Help
Dirk,
The values are as follows: SIGNWORK: 1. (Value List) "None";"Install";"Replace";"Repair";"Remove";"Relo cate";"Relocate & Replace";"Relocate & Repair" 2. one 3. one 4. blank FACETYPE 1. (Value List) “High Intensity”;”Diam. Grade” 2. one 3. one 4. blank Thanks, Cathy "Dirk Goldgar" wrote: "cathyt" wrote in message ... Peter and Dirk, Thanks for your replies. I've tried both suggestions and for each get an error message: Expressions contains Invalid syntax; may have entered an operand without an operator. It's always possible I made a mistake in my expression, but I'm not seeing it. You mentioned that [SIGNWORK] and [FACETYPE] are combo boxes. What are the values of these properties for each: Row Source Bound Column Column Count Column Widths -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup) |
#7
|
|||
|
|||
IIF Statement Help
"cathyt" wrote in message
... Dirk, The values are as follows: SIGNWORK: 1. (Value List) "None";"Install";"Replace";"Repair";"Remove";"Relo cate";"Relocate & Replace";"Relocate & Repair" 2. one 3. one 4. blank FACETYPE 1. (Value List) “High Intensity”;”Diam. Grade” 2. one 3. one 4. blank Somehow we've got "smart quotes" in some of those values, and in the IIf() expression -- things that look like simple double-quotes when view as plain text, but aren't. Edit your rowsource lists and replace all quotes with a freshly typed " character, and do the same with the ControlSource expression. When I do that in a test form, it all works fine. -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup) |
#8
|
|||
|
|||
IIF Statement Help
Dirk,
That was it exactly. It works exactly as I want it to now. Thanks for your time and expertise! Cathy "Dirk Goldgar" wrote: "cathyt" wrote in message ... Dirk, The values are as follows: SIGNWORK: 1. (Value List) "None";"Install";"Replace";"Repair";"Remove";"Relo cate";"Relocate & Replace";"Relocate & Repair" 2. one 3. one 4. blank FACETYPE 1. (Value List) “High Intensity”;”Diam. Grade” 2. one 3. one 4. blank Somehow we've got "smart quotes" in some of those values, and in the IIf() expression -- things that look like simple double-quotes when view as plain text, but aren't. Edit your rowsource lists and replace all quotes with a freshly typed " character, and do the same with the ControlSource expression. When I do that in a test form, it all works fine. -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup) |
Thread Tools | |
Display Modes | |
|
|