IIF Statement Help

  • Thread starter Thread starter cathyt
  • Start date Start date
C

cathyt

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
 
cathyt said:
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))
Click to expand...


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)
 
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
Click to expand...

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
 
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 said:
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.
Click to expand...

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,
The values are as follows:
SIGNWORK:
1. (Value List)
"None";"Install";"Replace";"Repair";"Remove";"Relocate";"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 said:
cathyt said:
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.
Click to expand...

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)
Click to expand...
 
cathyt said:
Dirk,
The values are as follows:
SIGNWORK:
1. (Value List)
"None";"Install";"Replace";"Repair";"Remove";"Relocate";"Relocate &
Replace";"Relocate & Repair"
2. one
3. one
4. blank

FACETYPE
1. (Value List) “High Intensityâ€;â€Diam. Gradeâ€
2. one
3. one
4. blank
Click to expand...


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,
That was it exactly. It works exactly as I want it to now. Thanks for your
time and expertise!
Cathy
 
Back
Top