syntax in long iif

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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...
 
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...
 
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...
 
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...
 
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...
 
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...
 
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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

iif, or, else? 3
Nested IIF Statement syntax(?) problem 4
IIF Error: invalid syntax, may have entered a comma w/o preceding 1
IIf Nz 4
Inner Join returns no records, how to be used in a calc.. 2
IIf statement Query 17
VBA to send files in folder 1
Using IIF in expression builder in reports 11

Back
Top