syntax in long iif

J

Jennyrd

I'm doing a long conditional statement that checks if 2 fields have 0 or >0
values. It must be a problem with the syntax I'm using. Here's my code that
is erroring out:

Currently: iif( [TableE].[Sites] >0 and [TableE].[Subjects]=0,"Sites
("&[TableE].[Sites]&")"), iif ([TableE].[Sites] =0 and
[TableE].[Subjects]>0,"Subjects (&[TableE].[Subjects]&")"), iif
([TableE].[Sites] >0 and [TableE].[Subjects]>0,"Sites ("&[TableE].[Sites]& ")
Subjects(" & [TableE].[Subjects] & ")")

Let me translate what I'm trying to do here for those who may be mystified
at my code :) :
if sites >0 and subjects=0 then Sites(1)
or
if sites=0 and subjects>0 then Subjects(15)
or
if sites and subjects >0 then Sites(8) Subjects(10)
Thanks a ton for any useful suggestions!!!
-Jenny
 
D

Dale Fye

You appear to have forgotten:
1. a " on line three
Currently: iif( [TableE].[Sites] >0 and [TableE].[Subjects]=0,"Sites
("&[TableE].[Sites]&")"), iif ([TableE].[Sites] =0 and
[TableE].[Subjects]>0,"Subjects (" & [TableE].[Subjects] &")"), iif
([TableE].[Sites] >0 and [TableE].[Subjects]>0,"Sites ("&[TableE].[Sites]& ")
Subjects(" & [TableE].[Subjects] & ")")
Click to expand...

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.


Jennyrd said:
I'm doing a long conditional statement that checks if 2 fields have 0 or >0
values. It must be a problem with the syntax I'm using. Here's my code that
is erroring out:

Currently: iif( [TableE].[Sites] >0 and [TableE].[Subjects]=0,"Sites
("&[TableE].[Sites]&")"), iif ([TableE].[Sites] =0 and
[TableE].[Subjects]>0,"Subjects (&[TableE].[Subjects]&")"), iif
([TableE].[Sites] >0 and [TableE].[Subjects]>0,"Sites ("&[TableE].[Sites]& ")
Subjects(" & [TableE].[Subjects] & ")")

Let me translate what I'm trying to do here for those who may be mystified
at my code :) :
if sites >0 and subjects=0 then Sites(1)
or
if sites=0 and subjects>0 then Subjects(15)
or
if sites and subjects >0 then Sites(8) Subjects(10)
Thanks a ton for any useful suggestions!!!
-Jenny
Click to expand...
 
J

Jennyrd

Thanks! Changed that but still getting the "You may have entered an invalid
comma or omitted quotaion marks" error.

Dale Fye said:
You appear to have forgotten:
1. a " on line three
Currently: iif( [TableE].[Sites] >0 and [TableE].[Subjects]=0,"Sites
("&[TableE].[Sites]&")"), iif ([TableE].[Sites] =0 and
[TableE].[Subjects]>0,"Subjects (" & [TableE].[Subjects] &")"), iif
([TableE].[Sites] >0 and [TableE].[Subjects]>0,"Sites ("&[TableE].[Sites]& ")
Subjects(" & [TableE].[Subjects] & ")")
Click to expand...

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.


Jennyrd said:
I'm doing a long conditional statement that checks if 2 fields have 0 or >0
values. It must be a problem with the syntax I'm using. Here's my code that
is erroring out:

Currently: iif( [TableE].[Sites] >0 and [TableE].[Subjects]=0,"Sites
("&[TableE].[Sites]&")"), iif ([TableE].[Sites] =0 and
[TableE].[Subjects]>0,"Subjects (&[TableE].[Subjects]&")"), iif
([TableE].[Sites] >0 and [TableE].[Subjects]>0,"Sites ("&[TableE].[Sites]& ")
Subjects(" & [TableE].[Subjects] & ")")

Let me translate what I'm trying to do here for those who may be mystified
at my code :) :
if sites >0 and subjects=0 then Sites(1)
or
if sites=0 and subjects>0 then Subjects(15)
or
if sites and subjects >0 then Sites(8) Subjects(10)
Thanks a ton for any useful suggestions!!!
-Jenny
Click to expand...
Click to expand...
 
J

John Spencer

Paretheses are your enemy. You need add a couple closing parentheses at the
end of the expression and remove them from the middle

IIF( [TableE].[Sites] >0 and [TableE].[Subjects]=0,
"Sites ("&[TableE].[Sites]&")",
IIF([TableE].[Sites] =0 and [TableE].[Subjects]>0,
"Subjects (&[TableE].[Subjects]&")",
IIF ([TableE].[Sites] >0 and [TableE].[Subjects]>0,
"Sites ("&[TableE].[Sites]& ") Subjects(" & [TableE].[Subjects] & ")" )))


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Jennyrd said:
I'm doing a long conditional statement that checks if 2 fields have 0 or
0
Click to expand...
values. It must be a problem with the syntax I'm using. Here's my code
that
is erroring out:

Currently: iif( [TableE].[Sites] >0 and [TableE].[Subjects]=0,"Sites
("&[TableE].[Sites]&")"), iif ([TableE].[Sites] =0 and
[TableE].[Subjects]>0,"Subjects (&[TableE].[Subjects]&")"), iif
([TableE].[Sites] >0 and [TableE].[Subjects]>0,"Sites ("&[TableE].[Sites]&
")
Subjects(" & [TableE].[Subjects] & ")")

Let me translate what I'm trying to do here for those who may be mystified
at my code :) :
if sites >0 and subjects=0 then Sites(1)
or
if sites=0 and subjects>0 then Subjects(15)
or
if sites and subjects >0 then Sites(8) Subjects(10)
Thanks a ton for any useful suggestions!!!
-Jenny
Click to expand...
 
J

Jennyrd

Thanks for your response. I made the suggested change and am getting "You
have entered an operand without an operator" Looks like Access is flagging
the comma between the iif statements. Any ideas? Could it be those
parentheses in the quotes causing this? Thanks for your help! It is greatly
appreciated.
-J

John Spencer said:
Paretheses are your enemy. You need add a couple closing parentheses at the
end of the expression and remove them from the middle

IIF( [TableE].[Sites] >0 and [TableE].[Subjects]=0,
"Sites ("&[TableE].[Sites]&")",
IIF([TableE].[Sites] =0 and [TableE].[Subjects]>0,
"Subjects (&[TableE].[Subjects]&")",
IIF ([TableE].[Sites] >0 and [TableE].[Subjects]>0,
"Sites ("&[TableE].[Sites]& ") Subjects(" & [TableE].[Subjects] & ")" )))


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Jennyrd said:
I'm doing a long conditional statement that checks if 2 fields have 0 or
0
Click to expand...
values. It must be a problem with the syntax I'm using. Here's my code
that
is erroring out:

Currently: iif( [TableE].[Sites] >0 and [TableE].[Subjects]=0,"Sites
("&[TableE].[Sites]&")"), iif ([TableE].[Sites] =0 and
[TableE].[Subjects]>0,"Subjects (&[TableE].[Subjects]&")"), iif
([TableE].[Sites] >0 and [TableE].[Subjects]>0,"Sites ("&[TableE].[Sites]&
")
Subjects(" & [TableE].[Subjects] & ")")

Let me translate what I'm trying to do here for those who may be mystified
at my code :) :
if sites >0 and subjects=0 then Sites(1)
or
if sites=0 and subjects>0 then Subjects(15)
or
if sites and subjects >0 then Sites(8) Subjects(10)
Thanks a ton for any useful suggestions!!!
-Jenny
Click to expand...
Click to expand...
 
J

Jennyrd

That's what it was!! Thanks so very much for everything!!
-J

Jennyrd said:
Thanks for your response. I made the suggested change and am getting "You
have entered an operand without an operator" Looks like Access is flagging
the comma between the iif statements. Any ideas? Could it be those
parentheses in the quotes causing this? Thanks for your help! It is greatly
appreciated.
-J

John Spencer said:
Paretheses are your enemy. You need add a couple closing parentheses at the
end of the expression and remove them from the middle

IIF( [TableE].[Sites] >0 and [TableE].[Subjects]=0,
"Sites ("&[TableE].[Sites]&")",
IIF([TableE].[Sites] =0 and [TableE].[Subjects]>0,
"Subjects (&[TableE].[Subjects]&")",
IIF ([TableE].[Sites] >0 and [TableE].[Subjects]>0,
"Sites ("&[TableE].[Sites]& ") Subjects(" & [TableE].[Subjects] & ")" )))


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Jennyrd said:
I'm doing a long conditional statement that checks if 2 fields have 0 or
0
values. It must be a problem with the syntax I'm using. Here's my code
that
is erroring out:

Currently: iif( [TableE].[Sites] >0 and [TableE].[Subjects]=0,"Sites
("&[TableE].[Sites]&")"), iif ([TableE].[Sites] =0 and
[TableE].[Subjects]>0,"Subjects (&[TableE].[Subjects]&")"), iif
([TableE].[Sites] >0 and [TableE].[Subjects]>0,"Sites ("&[TableE].[Sites]&
")
Subjects(" & [TableE].[Subjects] & ")")

Let me translate what I'm trying to do here for those who may be mystified
at my code :) :
if sites >0 and subjects=0 then Sites(1)
or
if sites=0 and subjects>0 then Subjects(15)
or
if sites and subjects >0 then Sites(8) Subjects(10)
Thanks a ton for any useful suggestions!!!
-Jenny
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Missed a quotation mark.

IIF( [TableE].[Sites] >0 and [TableE].[Subjects]=0,
"Sites (" & [TableE].[Sites] & ")",
IIF([TableE].[Sites] =0 and [TableE].[Subjects]>0,
"Subjects (" & [TableE].[Subjects] & ")",
IIF ([TableE].[Sites] >0 and [TableE].[Subjects]>0,
"Sites (" & [TableE].[Sites] & ") Subjects(" & [TableE].[Subjects] & ")" )))

By the way, I HATE nested IIF's. They are a pain to edit.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Jennyrd said:
Thanks for your response. I made the suggested change and am getting "You
have entered an operand without an operator" Looks like Access is
flagging
the comma between the iif statements. Any ideas? Could it be those
parentheses in the quotes causing this? Thanks for your help! It is
greatly
appreciated.
-J

John Spencer said:
Paretheses are your enemy. You need add a couple closing parentheses at
the
end of the expression and remove them from the middle

IIF( [TableE].[Sites] >0 and [TableE].[Subjects]=0,
"Sites ("&[TableE].[Sites]&")",
IIF([TableE].[Sites] =0 and [TableE].[Subjects]>0,
"Subjects (&[TableE].[Subjects]&")",
IIF ([TableE].[Sites] >0 and [TableE].[Subjects]>0,
"Sites ("&[TableE].[Sites]& ") Subjects(" & [TableE].[Subjects] & ")" )))


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Jennyrd said:
I'm doing a long conditional statement that checks if 2 fields have 0
or
0
values. It must be a problem with the syntax I'm using. Here's my
code
that
is erroring out:

Currently: iif( [TableE].[Sites] >0 and [TableE].[Subjects]=0,"Sites
("&[TableE].[Sites]&")"), iif ([TableE].[Sites] =0 and
[TableE].[Subjects]>0,"Subjects (&[TableE].[Subjects]&")"), iif
([TableE].[Sites] >0 and [TableE].[Subjects]>0,"Sites
("&[TableE].[Sites]&
")
Subjects(" & [TableE].[Subjects] & ")")

Let me translate what I'm trying to do here for those who may be
mystified
at my code :) :
if sites >0 and subjects=0 then Sites(1)
or
if sites=0 and subjects>0 then Subjects(15)
or
if sites and subjects >0 then Sites(8) Subjects(10)
Thanks a ton for any useful suggestions!!!
-Jenny
Click to expand...
Click to expand...
Click to expand...
 
J

John Nurick

Indentation helps make the logic clearer:

IIF([TableE].[Sites] >0 and [TableE].[Subjects]=0,
"Sites (" & [TableE].[Sites] & ")",
IIF([TableE].[Sites]=0 and [TableE].[Subjects]>0,
"Subjects (" & [TableE].[Subjects] & ")",
IIF([TableE].[Sites] >0 and [TableE].[Subjects]>0,
"Sites (" & [TableE].[Sites] & ") Subjects("
& [TableE].[Subjects] & ")"
)
)
)

and makes me suspect that this gets the same result with fewer
comparisons and no nesting:

IIF(TableE.Sites > 0, "Sites (" & TableE.Sites & ")", "")
& IIF(TableE.Sites > 0 AND TableE.Subjects > 0, " ", "")
& IIF(TableE.Subjects > 0, "Subjects (" & TableE.Subjects & ")", "")




Missed a quotation mark.

IIF( [TableE].[Sites] >0 and [TableE].[Subjects]=0,
"Sites (" & [TableE].[Sites] & ")",
IIF([TableE].[Sites] =0 and [TableE].[Subjects]>0,
"Subjects (" & [TableE].[Subjects] & ")",
IIF ([TableE].[Sites] >0 and [TableE].[Subjects]>0,
"Sites (" & [TableE].[Sites] & ") Subjects(" & [TableE].[Subjects] & ")" )))

By the way, I HATE nested IIF's. They are a pain to edit.
Click to expand...
 
Top