IIF Statement Help
in [Form]
|
Prev: Date format in a combo box
Next: Loopup Box
From: cathyt on 15 Apr 2010 11:06 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
From: Dirk Goldgar on 15 Apr 2010 11:15 "cathyt" <cathyt(a)discussions.microsoft.com> wrote in message news:7AA5BCBB-2199-4409-BBF4-ADC9815EF29E(a)microsoft.com... > 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)
From: XPS350 on 15 Apr 2010 11:20 On 15 apr, 17:06, cathyt <cat...(a)discussions.microsoft.com> 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
From: cathyt on 15 Apr 2010 14:40 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 >
From: Dirk Goldgar on 15 Apr 2010 15:36
"cathyt" <cathyt(a)discussions.microsoft.com> wrote in message news:5661C0D0-6BAE-47FC-8AB4-19FBE93B303C(a)microsoft.com... > 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) |