A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

IIF Statement Help



 
 
Thread Tools Display Modes
  #1  
Old April 15th, 2010, 04:06 PM posted to microsoft.public.access.forms
cathyt
external usenet poster
 
Posts: 13
Default 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  
Old April 15th, 2010, 04:15 PM posted to microsoft.public.access.forms
Dirk Goldgar
external usenet poster
 
Posts: 2,529
Default 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  
Old April 15th, 2010, 04:20 PM posted to microsoft.public.access.forms
XPS350
external usenet poster
 
Posts: 69
Default 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  
Old April 15th, 2010, 07:40 PM posted to microsoft.public.access.forms
cathyt
external usenet poster
 
Posts: 13
Default 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  
Old April 15th, 2010, 08:36 PM posted to microsoft.public.access.forms
Dirk Goldgar
external usenet poster
 
Posts: 2,529
Default 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  
Old April 16th, 2010, 01:00 PM posted to microsoft.public.access.forms
cathyt
external usenet poster
 
Posts: 13
Default 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  
Old April 16th, 2010, 03:18 PM posted to microsoft.public.access.forms
Dirk Goldgar
external usenet poster
 
Posts: 2,529
Default 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  
Old April 19th, 2010, 02:10 PM posted to microsoft.public.access.forms
cathyt
external usenet poster
 
Posts: 13
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:41 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 OfficeFrustration.
The comments are property of their posters.